Re: variable name in plpgsql

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