Re: Quick Pg/PLSQL question

"Matt Wagner" <mwagner@envex.net> writes:
> CREATE OR REPLACE FUNCTION test_func() RETURNS INTEGER AS '
> DECLARE
> BEGIN
>     INSERT INTO transaction_summary VALUES (61, 1, "now", 0, 0, 0, 0);
>     RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';

> NOTICE:  Error occurred while executing PL/pgSQL function test_func
> NOTICE:  line 4 at SQL statement
> ERROR:  Attribute 'now' not found

You would get the same error if you did that INSERT by hand, because
double-quoted "now" is completely different from single-quoted 'now'
in SQL --- one is an identifier equivalent to no-quotes-at-all now,
the other is a literal constant.  What you want here is a literal
constant that can be fed to the timestamp input routine.

You probably tried single-quoted 'now' already and got syntax errors
that you didn't understand.  The trick here is that the function body
is itself a single-quoted string literal.  To get single quotes into
the body of the function, you must either double 'em or backslash 'em.
So either of these should work:

    INSERT INTO transaction_summary VALUES (61, 1, ''now'', 0, 0, 0, 0);
    INSERT INTO transaction_summary VALUES (61, 1, \'now\', 0, 0, 0, 0);

This is covered in the manual, but perhaps it's not obvious till you
get bit by it...

            regards, tom lane