Thread: Quick Pg/PLSQL question

Hey everyone,

Trying to run a VERY simple function.  I've read through the Programmer's
Manual over and over, and continue to receive errors.  First, I ran the
following to add the language to the database:

    createlang plpgsql test

I then load the function using:

    \i test_func.sql

I then run the function by simply executing:

    select test_func() as answer;


The function I'm trying to execute is:

------------------------------------------

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';

------------------------------------------

Very simple function, but I continue to receive errors, because of the "now"
part.  However, in the Programmer's Manual, it specifically uses "now" with
double quotes in a couple examples.  When executing the function, I continue
receiving the following errors:

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


Any help would be greatly appreciated.

Thank you,
Matt Wagner

Envex Developments
Your CGI Script Specialists
http://www.envex.net/

On Fri, Sep 27, 2002 at 10:28:57PM -0500, Matt Wagner wrote:
>     INSERT INTO transaction_summary VALUES (61, 1, "now", 0, 0, 0, 0);

> Very simple function, but I continue to receive errors, because of the "now"
> part.  However, in the Programmer's Manual, it specifically uses "now" with
> double quotes in a couple examples.  When executing the function, I continue
> receiving the following errors:

Simple error. The manual uses two quote's, not double quotes (ie, '' vs ")
which probably look the same in many fonts.

Two quotes counts as an escaped quote whereas a double quote make postgresql
think it's an identifier, which doesn't work.

Hope tihs helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

"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