Thread: plpgsql Difference in behaviour between versions?

plpgsql Difference in behaviour between versions?

From
Chris McDonald
Date:
Hi,
I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a
databaseI've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not
legalsyntax in 9.0.5 but is legal in 8.4.8 please: 

===snip===
create type mytype
as
(
   somekey integer,
   open numeric(14, 2)
);

CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2))
    RETURNS VOID
AS $$
DECLARE
    somekey ALIAS FOR $1;
    rec mytype;
BEGIN
    rec.somekey = somekey;

        -- 9.0.5 will fail at the dot character in the
        -- following line here with syntax error sqlstate 42601
        -- but 8.4.8 is happy.
    rec.open = 32;
        -- ^

    RETURN;
END;
$$ LANGUAGE plpgsql;
===snip===

Re: plpgsql Difference in behaviour between versions?

From
Pavel Stehule
Date:
Hello

http://developer.postgresql.org/pgdocs/postgres/release-9-0.html

PL/pgSQL no longer allows variable names that match certain  reserved
words (Tom Lane)

use double quotes

 rec."open" = 32;

Regards

Pavel Stehule

2011/11/23 Chris McDonald <chrisjonmcdonald@gmail.com>:
> Hi,
> I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a
databaseI've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not
legalsyntax in 9.0.5 but is legal in 8.4.8 please: 
>
> ===snip===
> create type mytype
> as
> (
>   somekey integer,
>   open numeric(14, 2)
> );
>
> CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2))
>        RETURNS VOID
> AS $$
> DECLARE
>        somekey ALIAS FOR $1;
>        rec mytype;
> BEGIN
>        rec.somekey = somekey;
>
>        -- 9.0.5 will fail at the dot character in the
>        -- following line here with syntax error sqlstate 42601
>        -- but 8.4.8 is happy.
>        rec.open = 32;
>        -- ^
>
>        RETURN;
> END;
> $$ LANGUAGE plpgsql;
> ===snip===
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: plpgsql Difference in behaviour between versions?

From
Jerry Sievers
Date:
Chris McDonald <chrisjonmcdonald@gmail.com> writes:

> Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to
> postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed
> that the following works on 8.4.8 but does not work on 9.0.5. Can
> someone tell me why this is not legal syntax in 9.0.5 but is legal
> in 8.4.8 please:
>
> ===snip===
> create type mytype
> as
> (
>    somekey integer,
>    open numeric(14, 2)
> );
>
> CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2))
>     RETURNS VOID
> AS $$
> DECLARE
>     somekey ALIAS FOR $1;
>     rec mytype;
> BEGIN
>     rec.somekey = somekey;
>
>         -- 9.0.5 will fail at the dot character in the
>         -- following line here with syntax error sqlstate 42601
>         -- but 8.4.8 is happy.
>     rec."open" = 32;

Hmmm, I do not see that open is a reserved word but the PL must be
treating it special somehow.

See where I've added quotes above which gets it working on my 9.1
instance.

HTH

>         -- ^
>
>     RETURN;
> END;
> $$ LANGUAGE plpgsql;
> ===snip===
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

Re: plpgsql Difference in behaviour between versions?

From
Jerry Sievers
Date:
Chris McDonald <chrisjonmcdonald@gmail.com> writes:

> Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to
> postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed
> that the following works on 8.4.8 but does not work on 9.0.5. Can
> someone tell me why this is not legal syntax in 9.0.5 but is legal
> in 8.4.8 please:
>
> ===snip===
> create type mytype
> as
> (
>    somekey integer,
>    open numeric(14, 2)
> );
>
> CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2))
>     RETURNS VOID
> AS $$
> DECLARE
>     somekey ALIAS FOR $1;
>     rec mytype;
> BEGIN
>     rec.somekey = somekey;
>
>         -- 9.0.5 will fail at the dot character in the
>         -- following line here with syntax error sqlstate 42601
>         -- but 8.4.8 is happy.
>     rec.open = 32;

I wonder if this remark in the release notes is relevant.  Tom will
probably shed some light here.  I don't get the connection but we'll
see.

  * Improve handling of cases where PL/pgSQL variable names conflict with identifiers used in queries within a
    function (Tom Lane)

    The default behavior is now to throw an error when there is a conflict, so as to avoid surprising behaviors. This
    can be modified, via the configuration parameter plpgsql.variable_conflict or the per-function option #
    variable_conflict, to allow either the variable or the query-supplied column to be used. In any case PL/pgSQL will
    no longer attempt to substitute variables in places where they would not be syntactically valid.


>
>     RETURN;
> END;
> $$ LANGUAGE plpgsql;
> ===snip===
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

Re: plpgsql Difference in behaviour between versions?

From
Tom Lane
Date:
Jerry Sievers <gsievers19@comcast.net> writes:
> Hmmm, I do not see that open is a reserved word but the PL must be
> treating it special somehow.

plpgsql has a different list of reserved words than the main SQL grammar
does.  I don't think we explicitly document it anywhere, but pretty much
any keyword that can start a plpgsql command is considered reserved by
plpgsql (since otherwise it'd be ambiguous against an assignment to a
plpgsql variable of the same name).

            regards, tom lane

Re: plpgsql Difference in behaviour between versions?

From
Chris McDonald
Date:
OK, I see it is the term open which fails the syntax checker - I guessed this might be because open is a reserved word
buthttp://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html does not indicate whether open is either
reservedor not in postgresql. 

Checking 8.4 doco, http://www.postgresql.org/docs/8.4/static/sql-keywords-appendix.html has the same detail when it
comesto 'reservedness' of the term open. 

Still interested in a definitive answer, but the fix for me appears to be simply to change the word open to something
else.

Re: plpgsql Difference in behaviour between versions?

From
Adrian Klaver
Date:
On Wednesday, November 23, 2011 11:43:04 am Chris McDonald wrote:
> OK, I see it is the term open which fails the syntax checker - I guessed
> this might be because open is a reserved word but
> http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html
> does not indicate whether open is either reserved or not in postgresql.

The relevant part from above is:

"As a general rule, if you get spurious parser errors for commands that contain
any of the listed key words as an identifier you should try to quote the
identifier to see if the problem goes away. "

>

> Still interested in a definitive answer, but the fix for me appears to be
> simply to change the word open to something else.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: plpgsql Difference in behaviour between versions?

From
Tom Lane
Date:
Chris McDonald <chrisjonmcdonald@gmail.com> writes:
> Still interested in a definitive answer, but the fix for me appears to be simply to change the word open to something
else.

Well, if you want a definitive answer, you can consult the list of
plpgsql reserved words here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/pl/plpgsql/src/pl_scanner.c;h=76e8436e50e9c1d4919a60916b0cabfa83a13eb1;hb=HEAD#l30
where you will find that "open" is indeed a reserved word.

            regards, tom lane