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>:
Show quoted text
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 1Output I expected:
CREATE FUNCTIONThe 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.--
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
Show quoted text
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. 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 FUNCTIONWhy should it succeed with "bar" but not with "foo"?
because bar isn't declared as variable
regards
Pavel
Show quoted text
Thanks,
Keith2009/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;';
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 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. 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 FUNCTIONWhy 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
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
Show quoted text
...Robert