Re: allowed variable names in functions?

Hello.
I suspect that in a plpgsql function

DECLARE
c2 REAL;
cadiv REAL;

works but

c2 REAL;
c2div REAL;

doesn't.

Is this true, and if so, what are the rules for the names in the
function? I use 8.1.

Hello

it works in my 8.1

postgres=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
postgres=# create or replace function foo(a int) returns void as
$$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language
plpgsql;
CREATE FUNCTION
postgres=# select foo(10);
foo
-----

(1 row)
Regards
Pavel Stehule

2008/6/30 A B <gentosaker(at)gmail(dot)com>:
> Hello.
> I suspect that in a plpgsql function
>
> DECLARE
> c2 REAL;
> cadiv REAL;
>
> works but
>
> c2 REAL;
> c2div REAL;
>
> doesn't.
>
> Is this true, and if so, what are the rules for the names in the
> function? I use 8.1.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Then my assumption was wrong.
Here is the entire function and it fails with the names
c2,c2div,c3,c3div, but if names are changed, it works!
(by works I mean I get the "hello" lines printed) There is nothing
wrong with the select statement either, that works fine if I run it
stand-alone, or with the names of c2,c2div,c3,c3div changed.

CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
DECLARE
c2 REAL;
c2div REAL;
c3 REAL;
c3div REAL;
weights RECORD;
tmp RECORD;
retval RECORD;
t RECORD;
BEGIN
RAISE NOTICE 'starting...';
FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP
RAISE NOTICE 'hello %',tmp.id;
END LOOP;
RETURN;
END; $$ LANGUAGE plpgsql;

2008/6/30 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> it works in my 8.1
>
> postgres=# CREATE LANGUAGE plpgsql;
> CREATE LANGUAGE
> postgres=# create or replace function foo(a int) returns void as
> $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language
> plpgsql;
> CREATE FUNCTION
> postgres=# select foo(10);
> foo
> -----
>
> (1 row)
> Regards
> Pavel Stehule
>
> 2008/6/30 A B <gentosaker(at)gmail(dot)com>:
>> Hello.
>> I suspect that in a plpgsql function
>>
>> DECLARE
>> c2 REAL;
>> cadiv REAL;
>>
>> works but
>>
>> c2 REAL;
>> c2div REAL;
>>
>> doesn't.
>>
>> Is this true, and if so, what are the rules for the names in the
>> function? I use 8.1.

By changing c2,c3,etc. to something else xc2,xc3, etc. it worked!
So was the problem that I refered to the same names in the SELECT statement?

2008/6/30 A B <gentosaker(at)gmail(dot)com>:
> Then my assumption was wrong.
> Here is the entire function and it fails with the names
> c2,c2div,c3,c3div, but if names are changed, it works!
> (by works I mean I get the "hello" lines printed) There is nothing
> wrong with the select statement either, that works fine if I run it
> stand-alone, or with the names of c2,c2div,c3,c3div changed.
>
> CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
> DECLARE
> c2 REAL;
> c2div REAL;
> c3 REAL;
> c3div REAL;
> BEGIN
> FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP

am Mon, dem 30.06.2008, um 12:38:40 +0200 mailte A B folgendes:
> Then my assumption was wrong.
> Here is the entire function and it fails with the names
> c2,c2div,c3,c3div, but if names are changed, it works!
> (by works I mean I get the "hello" lines printed) There is nothing
> wrong with the select statement either, that works fine if I run it
> stand-alone, or with the names of c2,c2div,c3,c3div changed.
>
> CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
> DECLARE
> c2 REAL;
> c2div REAL;
> c3 REAL;
> c3div REAL;
> weights RECORD;
> tmp RECORD;
> retval RECORD;
> t RECORD;
> BEGIN
> RAISE NOTICE 'starting...';
> FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP
> RAISE NOTICE 'hello %',tmp.id;
> END LOOP;
> RETURN;
> END; $$ LANGUAGE plpgsql;

Don't use the same names for plpgsql-variables and for column names.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

I see one big problem. You have colision between column and variable
names! When you has SQL inside function use prefix for variables or
use qualified names.

DECLARE a varchar;
BEGIN
FOR a IN SELECT a FROM ... -- is bug

you have to do
DELARE _a varchar;
BEGIN
FOR _a IN SELECT t.a FROM tab t ...

Regards
Pavel Stehule

2008/6/30 A B <gentosaker(at)gmail(dot)com>:
> Then my assumption was wrong.
> Here is the entire function and it fails with the names
> c2,c2div,c3,c3div, but if names are changed, it works!
> (by works I mean I get the "hello" lines printed) There is nothing
> wrong with the select statement either, that works fine if I run it
> stand-alone, or with the names of c2,c2div,c3,c3div changed.
>
> CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
> DECLARE
> c2 REAL;
> c2div REAL;
> c3 REAL;
> c3div REAL;
> weights RECORD;
> tmp RECORD;
> retval RECORD;
> t RECORD;
> BEGIN
> RAISE NOTICE 'starting...';
> FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP
> RAISE NOTICE 'hello %',tmp.id;
> END LOOP;
> RETURN;
> END; $$ LANGUAGE plpgsql;
>
>
> 2008/6/30 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> Hello
>>
>> it works in my 8.1
>>
>> postgres=# CREATE LANGUAGE plpgsql;
>> CREATE LANGUAGE
>> postgres=# create or replace function foo(a int) returns void as
>> $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language
>> plpgsql;
>> CREATE FUNCTION
>> postgres=# select foo(10);
>> foo
>> -----
>>
>> (1 row)
>> Regards
>> Pavel Stehule
>>
>> 2008/6/30 A B <gentosaker(at)gmail(dot)com>:
>>> Hello.
>>> I suspect that in a plpgsql function
>>>
>>> DECLARE
>>> c2 REAL;
>>> cadiv REAL;
>>>
>>> works but
>>>
>>> c2 REAL;
>>> c2div REAL;
>>>
>>> doesn't.
>>>
>>> Is this true, and if so, what are the rules for the names in the
>>> function? I use 8.1.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mon, Jun 30, 2008 at 12:44:31PM +0200, A B wrote:

> By changing c2,c3,etc. to something else xc2,xc3, etc. it worked!
> So was the problem that I refered to the same names in the SELECT statement?
Yes.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Thank you both! :-)

But there not any problem with returning values with code like this

DECLARE
retval RECORD;

retval.c2 := ....
RETRUN NEXT retval;

if c2 is a field on some table?

2008/6/30 Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>:
> On Mon, Jun 30, 2008 at 12:44:31PM +0200, A B wrote:
>
>> By changing c2,c3,etc. to something else xc2,xc3, etc. it worked!
>> So was the problem that I refered to the same names in the SELECT statement?
> Yes.
>
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

> But there not any problem with returning values with code like this
>
> DECLARE
> retval RECORD;
>
> retval.c2 := ....
> RETRUN NEXT retval;
>
> if c2 is a field on some table?

Oh, that seems also to be problematic.

A B wrote:
> Here is the entire function and it fails with the names
> c2,c2div,c3,c3div, but if names are changed, it works!
> (by works I mean I get the "hello" lines printed) There is nothing
> wrong with the select statement either, that works fine if I run it
> stand-alone, or with the names of c2,c2div,c3,c3div changed.
>
> CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$
> DECLARE
> c2 REAL;
> c2div REAL;
> c3 REAL;
> c3div REAL;
> weights RECORD;
> tmp RECORD;
> retval RECORD;
> t RECORD;
> BEGIN
> RAISE NOTICE 'starting...';
> FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP
> RAISE NOTICE 'hello %',tmp.id;
> END LOOP;
> RETURN;
> END; $$ LANGUAGE plpgsql;

That is because c2 and c3 in the SELECT statement are replaced with
the variables before the SQL statement is executed.

See the documentation:
http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

You should qualify the column names:

SELECT master.id, master.c2, master.c3 FROM master WHERE ... AND master.c3 <> 0

Yours,
Laurenz Albe