Re: Quick Pg/PLSQL question

"Matt Wagner" <mwagner(at)envex(dot)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