help on qouteing in plpgsql function
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
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 #
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
Show quoted text
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)
Robert Treat <rtreat@webmd.net> writes:
strDelete := ''DELETE FROM ''
|| quote_ident(arrTables.table_name)
|| '' WHERE timestamp < now() - ((''''''
|| arrTables.historysize
|| '' days'''' )::interval) '';
EXECUTE strDelete;
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.
Seems like it'd be better to use quote_literal, viz
strDelete := ''DELETE FROM ''
|| quote_ident(arrTables.table_name)
|| '' WHERE timestamp < now() - ''
|| quote_literal(arrTables.historysize || '' days'')
|| ''::interval'';
EXECUTE strDelete;
regards, tom lane