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 по дате отправления:

Предыдущее
От: Patrick Macdonald
Дата:
Сообщение: Re: read this and puke
Следующее
От: wsheldah@lexmark.com
Дата:
Сообщение: Re: Fw: read this and puke