Thread: plpgsql Difference in behaviour between versions?
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===
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 >
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
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
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
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.
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
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