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(dot)klaver(at)gmail(dot)com