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