Thread: BUG #5032: unexpected syntax error for plpgsql function returns table
The following bug has been logged online:
Bug reference: 5032
Logged by: Keith Cascio
Email address: keith@cs.ucla.edu
PostgreSQL version: 8.4.0
Operating system: CentOS 5.3 (Linux)
Description: unexpected syntax error for plpgsql function returns
table
Details:
Do this:
create function reproduce() returns table(foo integer) language plpgsql as
'begin return query select 1 foo; end;';
Output I got:
ERROR: syntax error at or near "$1"
LINE 1: select 1 $1
^
QUERY: select 1 $1
CONTEXT: SQL statement in PL/PgSQL function "reproduce" near line 1
Output I expected:
CREATE FUNCTION
The mechanism of this error involves the "foo" return column and the "foo"
alias being identical. Identifier collision? If I change one of the "foo"s
to "bar", the definition succeeds. There are fancier ways to cause similar
unexpected syntax errors, but this is close to a minimal example.
Hello it's not bug - PostgreSQL doesn't support parameter placeholder on this position. Use dynamic query instead - plpgsql statement EXECUTE. regards Pavel Stehule 2009/9/3 Keith Cascio <keith@cs.ucla.edu>: > > The following bug has been logged online: > > Bug reference: =C2=A0 =C2=A0 =C2=A05032 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Keith Cascio > Email address: =C2=A0 =C2=A0 =C2=A0keith@cs.ucla.edu > PostgreSQL version: 8.4.0 > Operating system: =C2=A0 CentOS 5.3 (Linux) > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0unexpected syntax error for plpgs= ql function returns > table > Details: > > Do this: > create function reproduce() returns table(foo integer) language plpgsql as > 'begin return query select 1 foo; end;'; > > Output I got: > ERROR: =C2=A0syntax error at or near "$1" > LINE 1: =C2=A0select 1 =C2=A0$1 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^ > QUERY: =C2=A0 select 1 =C2=A0$1 > CONTEXT: =C2=A0SQL statement in PL/PgSQL function "reproduce" near line 1 > > Output I expected: > CREATE FUNCTION > > The mechanism of this error involves the "foo" return column and the "foo" > alias being identical. =C2=A0Identifier collision? =C2=A0If I change one = of the "foo"s > to "bar", the definition succeeds. =C2=A0There are fancier ways to cause = similar > unexpected syntax errors, but this is close to a minimal example. > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
Pavel, On Thu, 3 Sep 2009, Pavel Stehule wrote: > it's not bug - PostgreSQL doesn't support parameter placeholder on this > position. Use dynamic query instead - plpgsql statement EXECUTE. Thank you for your reply. I appreciate your suggestion, but it still seems like a bug to me. Please comment on the fact that the following code succeeds: create function reproduce() returns table(foo integer) language plpgsql as 'begin return query select 1 bar; end;'; Output in psql is: CREATE FUNCTION Why should it succeed with "bar" but not with "foo"? Thanks, Keith > 2009/9/3 Keith Cascio <keith@cs.ucla.edu>: > > create function reproduce() returns table(foo integer) language plpgsql as > > 'begin return query select 1 foo; end;';
2009/9/3 Keith Cascio <keith@cs.ucla.edu>: > Pavel, > > On Thu, 3 Sep 2009, Pavel Stehule wrote: > >> it's not bug - PostgreSQL doesn't support parameter placeholder on this >> position. Use dynamic query instead - plpgsql statement EXECUTE. > > Thank you for your reply. =C2=A0I appreciate your suggestion, but it stil= l seems like > a bug to me. =C2=A0Please comment on the fact that the following code suc= ceeds: > > create function reproduce() returns table(foo integer) language plpgsql as > 'begin return query select 1 bar; end;'; > > Output in psql is: > CREATE FUNCTION > > Why should it succeed with "bar" but not with "foo"? because bar isn't declared as variable regards Pavel > > Thanks, > Keith > > >> 2009/9/3 Keith Cascio <keith@cs.ucla.edu>: >> > create function reproduce() returns table(foo integer) language plpgsq= l as >> > 'begin return query select 1 foo; end;'; >
On Thu, Sep 3, 2009 at 12:48 AM, Keith Cascio<keith@cs.ucla.edu> wrote: > Pavel, > > On Thu, 3 Sep 2009, Pavel Stehule wrote: > >> it's not bug - PostgreSQL doesn't support parameter placeholder on this >> position. Use dynamic query instead - plpgsql statement EXECUTE. > > Thank you for your reply. =A0I appreciate your suggestion, but it still s= eems like > a bug to me. =A0Please comment on the fact that the following code succee= ds: > > create function reproduce() returns table(foo integer) language plpgsql as > 'begin return query select 1 bar; end;'; > > Output in psql is: > CREATE FUNCTION > > Why should it succeed with "bar" but not with "foo"? This is a very common gotcha in plpgsql. I always prefix function arguments and and locals with _; merlin
Pavel, On Thu, 3 Sep 2009, Pavel Stehule wrote: > 2009/9/3 Keith Cascio <keith@cs.ucla.edu>: > > Why should it succeed with "bar" but not with "foo"? > > because bar isn't declared as variable I understand now. returns table(v1 t1, v2 t2,...) is equivalent to declaring OUT parameters, therefore "foo" is an out parameter. Thank you for your patience sir. Keith
On Sep 2, 2009, at 11:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > it's not bug - PostgreSQL doesn't support parameter placeholder on > this position. Use dynamic query instead - plpgsql statement EXECUTE. It may not be a bug exactly, but it sure isn't a feature. ...Robert
2009/9/3 Robert Haas <robertmhaas@gmail.com>: > On Sep 2, 2009, at 11:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> Hello >> >> it's not bug - PostgreSQL doesn't support parameter placeholder on >> this position. Use dynamic query instead - plpgsql statement EXECUTE. > > It may not be a bug exactly, but it sure isn't a feature. I hope so this problem will be solved at 8.5 pavel > > ...Robert >