Thread: 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@gmail.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@postgresql.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.stehule@gmail.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@gmail.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@gmail.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@gmail.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.stehule@gmail.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@gmail.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@postgresql.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.Hilbert@gmx.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@postgresql.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