Re: allowed variable names in functions? - Mailing list pgsql-general

From Pavel Stehule
Subject Re: allowed variable names in functions?
Date
Msg-id 162867790806300348s4f74149au10e18fa5001cff05@mail.gmail.com
Whole thread Raw
In response to Re: allowed variable names in functions?  ("A B" <gentosaker@gmail.com>)
List pgsql-general
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
>

pgsql-general by date:

Previous
From: "A. Kretschmer"
Date:
Subject: Re: allowed variable names in functions?
Next
From: "EBIHARA, Yuichiro"
Date:
Subject: server log files