help on qouteing in plpgsql function
От | Robert Treat |
---|---|
Тема | help on qouteing in plpgsql function |
Дата | |
Msg-id | 1024081111.6156.27.camel@camel обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: