Thread: pl/pgsql breakage in 8.1b4?

pl/pgsql breakage in 8.1b4?

From
Philip Yarra
Date:
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;
beginupdate ptestset bar = modify_ptest.barwhere 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.



Re: pl/pgsql breakage in 8.1b4?

From
Stephan Szabo
Date:
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.


Re: pl/pgsql breakage in 8.1b4?

From
Philip Yarra
Date:
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.



Re: pl/pgsql breakage in 8.1b4?

From
Tom Lane
Date:
Philip Yarra <philip@utiba.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


Re: pl/pgsql breakage in 8.1b4?

From
Philip Yarra
Date:
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.



Re: pl/pgsql breakage in 8.1b4?

From
Tom Lane
Date:
Philip Yarra <philip@utiba.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


Re: pl/pgsql breakage in 8.1b4?

From
Philip Yarra
Date:
On Fri, 28 Oct 2005 03:03 pm, Tom Lane wrote:
> Philip Yarra <philip@utiba.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.


Re: pl/pgsql breakage in 8.1b4?

From
"Jim C. Nasby"
Date:
On Fri, Oct 28, 2005 at 12:10:00AM -0400, Tom Lane wrote:
> Philip Yarra <philip@utiba.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461