help on qouteing in plpgsql function

From: Robert Treat <rtreat(at)webmd(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: help on qouteing in plpgsql function
Date: 2002-06-14 18:58:31
Message-ID: 1024081111.6156.27.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I think I am at the point of just guessing now, so I'm hoping someone
can shed a little light on this. Heres the code:

CREATE OR REPLACE FUNCTION purge_old_messages()
RETURNS bool
AS
'DECLARE

arrTables RECORD;
strDelete TEXT;

BEGIN
FOR arrTables IN SELECT table_name,historysize FROM table_info WHERE
historysize > 0 AND table_name like ''msg%'' LOOP

strDelete := ''DELETE FROM ''
|| qoute_ident(arrTables.table_name)
|| '' WHERE timestamp < now() - ((''''
|| quote_literal(arrTables.historysize)
|| '' days '''')::interval)'';

EXECUTE strDelete;

END LOOP;

RETURN ''t'';

END;'

LANGUAGE 'plpgsql';

What I am trying to accomplish with the second sql query is:

DELETE FROM mytable WHERE timestamp < now() - ('mynumber
days')::interval

I'm pretty sure my problem stems from the need to quote mynumber when
casting as an interval. If I do the above and run the query, I get

rms=# select purge_old_messages();
NOTICE: plpgsql: ERROR during compile of purge_old_messages near line 9
ERROR: mismatched parentheses

If I do it like

|| '' days '')::interval)'';

i get

rms=# select purge_old_messages();
NOTICE: Error occurred while executing PL/pgSQL function
purge_old_messages
NOTICE: line 9 at assignment
ERROR: parser: parse error at or near "days"

I'm sure I am just missing a ' or two somewhere, hopefully someone can
spot it?

Thanks in advance,
Robert Treat

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Macdonald 2002-06-14 19:04:23 Re: read this and puke
Previous Message Neil Conway 2002-06-14 18:43:06 Re: Store / Retrieve image files