Thread: 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

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