Good ole printf... for those keeping score at home, the solution was:
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 ''
|| quote_ident(arrTables.table_name)
|| '' WHERE timestamp < now() - ((''''''
|| arrTables.historysize
|| '' days'''' )::interval) '';
EXECUTE strDelete;
END LOOP;
RETURN ''t'';
END;'
LANGUAGE 'plpgsql';
Note that no "quote_foo" function was needed on the second variable
since I had to enclose it along with other text within my own quotes.
Robert Treat
On Mon, 2002-06-17 at 09:25, Jan Wieck wrote:
> Robert Treat wrote:
> >
> > 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
> > '[...]
> >
> > What I am trying to accomplish with the second sql query is:
>
> What about putting a
>
> RAISE NOTICE ''strDelete = %'', strDelete;
>
> before the EXECUTE and continue with try'n'error?
> Good old "printf-debugging" allways works :-)
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck@Yahoo.com #
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
