Обсуждение: variable name in plpgsql

It seems that one cannot use the variable name that is the same as the
column name of a relation like in the following function

------
create or replace function bla() returns void language plpgsql as $$
declare
    email varchar;
begin
    select email into email from pass where id=1;
    raise notice 'pass is %',email;
end;
$$
------
I get null value email and postgres 8.4.4 does not warn nor raise
error on creating;
I know for a fact that email must not be null; it's not null when I
rename the variable
email to something else.

As far as I can see this isn't documented anywhere.  Is this because
it's common/insider knowledge?  Can someone confirm?

Thanks

On Wednesday 08 September 2010 5:23:20 pm zhong ming wu wrote:
> It seems that one cannot use the variable name that is the same as the
> column name of a relation like in the following function
>
> ------
> create or replace function bla() returns void language plpgsql as $$
> declare
>     email varchar;
> begin
>     select email into email from pass where id=1;
>     raise notice 'pass is %',email;
> end;
> $$
> ------
> I get null value email and postgres 8.4.4 does not warn nor raise
> error on creating;
> I know for a fact that email must not be null; it's not null when I
> rename the variable
> email to something else.
>
> As far as I can see this isn't documented anywhere.  Is this because
> it's common/insider knowledge?  Can someone confirm?
>
> Thanks

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html

"Caution

PL/pgSQL will substitute for any identifier matching one of the function's
declared variables; it is not bright enough to know whether that's what you
meant! Thus, it is a bad idea to use a variable name that is the same as any
table, column, or function name that you need to reference in commands within
the function. For more discussion see Section 38.10.1. "

--
Adrian Klaver
adrian.klaver@gmail.com

zhong ming wu wrote:
> It seems that one cannot use the variable name that is the same as the
> column name of a relation like in the following function
>
> ------
> create or replace function bla() returns void language plpgsql as $$
> declare
>     email varchar;
> begin
>     select email into email from pass where id=1;
>     raise notice 'pass is %',email;
> end;
> $$
> ------
> I get null value email and postgres 8.4.4 does not warn nor raise
> error on creating;
> I know for a fact that email must not be null; it's not null when I
> rename the variable
> email to something else.

All function lexical variables and parameters can be qualified with the function
name, so you can say "bla.email" to refer to the variable unambiguously.  And
you can qualify a table column with the table name, like "pass.email" to make
that unambiguous.

> As far as I can see this isn't documented anywhere.  Is this because
> it's common/insider knowledge?  Can someone confirm?

It is documented, in
http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html but I do
agree that it can be a challenge to find what section(s) of the manual has the
information one seeks.

-- Darren Duncan