Re: pl/pgsql breakage in 8.1b4?

Just testing pl/pgsql functions in 8.1beta4, I see failures for syntax that
works in 8.0.3. The simplest test case for this is:

create table ptest(foo int, bar varchar(10));
create or replace function modify_ptest(
foo int,
bar varchar)
returns numeric as $$
declare
res numeric;
begin
update ptest
set bar = modify_ptest.bar
where foo = modify_ptest.foo;
res := 0;
return res;
end;
$$ LANGUAGE plpgsql;

The error message from psql is:

ERROR: syntax error at or near "$1" at character 19
QUERY: update ptest set $1 = modify_ptest.bar where $2 = modify_ptest.foo
CONTEXT: SQL statement in PL/PgSQL function "modify_ptest" near line 7
LINE 1: update ptest set $1 = modify_ptest.bar where $2 = modify...
^

I assume what I'm trying should still work, though I couldn't find comparable
examples in the doco. Looks to me like a problem with parameter aliasing.

Regards, Philip.

-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.

On Fri, 28 Oct 2005, Philip Yarra wrote:

> Just testing pl/pgsql functions in 8.1beta4, I see failures for syntax that
> works in 8.0.3. The simplest test case for this is:

The function below fails for me similarly in 8.0.3 on execution. 8.1
merely tells you at creation time.

Using bar and foo as both parameter names and the field names doesn't
really work.

> create table ptest(foo int, bar varchar(10));
> create or replace function modify_ptest(
> foo int,
> bar varchar)
> returns numeric as $$
> declare
> res numeric;
> begin
> update ptest
> set bar = modify_ptest.bar
> where foo = modify_ptest.foo;
> res := 0;
> return res;
> end;
> $$ LANGUAGE plpgsql;
>
> The error message from psql is:
>
> ERROR: syntax error at or near "$1" at character 19
> QUERY: update ptest set $1 = modify_ptest.bar where $2 = modify_ptest.foo
> CONTEXT: SQL statement in PL/PgSQL function "modify_ptest" near line 7
> LINE 1: update ptest set $1 = modify_ptest.bar where $2 = modify...
> ^
>
> I assume what I'm trying should still work, though I couldn't find comparable
> examples in the doco. Looks to me like a problem with parameter aliasing.

On Fri, 28 Oct 2005 01:37 pm, Stephan Szabo wrote:
> The function below fails for me similarly in 8.0.3 on execution. 8.1
> merely tells you at creation time.

Ah, good point... "works" for very small values of "works" then :-) My
mistake.

> Using bar and foo as both parameter names and the field names doesn't
> really work.

Fair enough. I was fooled because it works this way in Oracle (well, at any
rate, that's what's in the stored procs I'm porting) and it didn't fail at
create time in 8.0.3.

Without really wishing to volunteer myself: should plpgsql allow using
parameters with the same name as the columns being referred to within the
function, provided they're qualified as function_name.parameter?

Philip.

-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.

Philip Yarra <philip(at)utiba(dot)com> writes:
> Without really wishing to volunteer myself: should plpgsql allow using
> parameters with the same name as the columns being referred to within the
> function, provided they're qualified as function_name.parameter?

No, because that just changes where the ambiguity is. The function name
could easily conflict with a table name. It's a mighty weird-looking
convention anyway --- on what grounds would you argue that the function
is a structure having parameter names as fields?

regards, tom lane

On Fri, 28 Oct 2005 02:10 pm, Tom Lane wrote:
> > Without really wishing to volunteer myself: should plpgsql allow using
> > parameters with the same name as the columns being referred to within the
> > function, provided they're qualified as function_name.parameter?
>
> No, because that just changes where the ambiguity is. The function name
> could easily conflict with a table name.

Yup, I guess it could.

> It's a mighty weird-looking
> convention anyway --- on what grounds would you argue that the function
> is a structure having parameter names as fields?

I wasn't arguing either way, I was just curious.

Hmmm... is it feasible to make the error message a little more useful? People
who didn't use the old-style positional parameters might not understand where
$1 and $2 are coming from.

Regards, Philip.

-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.

Philip Yarra <philip(at)utiba(dot)com> writes:
> Hmmm... is it feasible to make the error message a little more useful?
> People who didn't use the old-style positional parameters might not
> understand where $1 and $2 are coming from.

Not sure how --- the arm's-length relationship between plpgsql and the
main parser hurts us here.

regards, tom lane

On Fri, 28 Oct 2005 03:03 pm, Tom Lane wrote:
> Philip Yarra <philip(at)utiba(dot)com> writes:
> > Hmmm... is it feasible to make the error message a little more useful?
> > People who didn't use the old-style positional parameters might not
> > understand where $1 and $2 are coming from.
>
> Not sure how --- the arm's-length relationship between plpgsql and the
> main parser hurts us here.

Yeah, I had a suspicion the answer might be along those lines.

Well, here's a minor doco patch against HEAD to at least record this with the
Oracle PL/sql -> PL/pgSQL porting notes.

Regards, Philip.

-----------------
Utiba Pty Ltd
This message has been scanned for viruses and
dangerous content by Utiba mail server and is
believed to be clean.

On Fri, Oct 28, 2005 at 12:10:00AM -0400, Tom Lane wrote:
> Philip Yarra <philip(at)utiba(dot)com> writes:
> > Without really wishing to volunteer myself: should plpgsql allow using
> > parameters with the same name as the columns being referred to within the
> > function, provided they're qualified as function_name.parameter?
>
> No, because that just changes where the ambiguity is. The function name
> could easily conflict with a table name. It's a mighty weird-looking
> convention anyway --- on what grounds would you argue that the function
> is a structure having parameter names as fields?

Is there some other means we could come up with to distinguish between
field names and variables? Maybe local.variablename?

Oracle has similar issues where you have to use
functionname.variablename if there's a conflict, which is a pita. Hence
the standard advice of always prefixing your variables with something,
but that seems like an ugly hack to me. Of course the real issue is the
namespace conflict to begin with, but I have no idea how to solve that..
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461