I just got the following example:

DROP TABLE foo;
CREATE TABLE foo (login varchar(100));
INSERT INTO foo values ('abc');

DROP FUNCTION footest1(varchar(100));
CREATE FUNCTION footest1(varchar(100)) RETURNS varchar(100) AS '
        DECLARE
                login varchar(100);
        BEGIN
                SELECT INTO login login FROM foo LIMIT 1;
                RETURN login;
        END;
' LANGUAGE 'plpgsql';

DROP FUNCTION footest2(varchar(100));
CREATE FUNCTION footest2(varchar(100)) RETURNS varchar(100) AS '
        DECLARE
                fieldname varchar(100);
        BEGIN
                SELECT INTO fieldname login FROM foo LIMIT 1;
                RETURN fieldname;
        END;
' LANGUAGE 'plpgsql';

SELECT footest1('foobar');
SELECT footest2('foobar');

The first select returns NULL while the second correctly returns 'abc'.
I just wonder why it is that way. The only difference seems to be the
name of the variable which in footest1 equals the attribute name. 

Now I can guess what happens but I wonder if this is the desired
behaviour.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Mon, 12 Aug 2002 15:26:03 +0200
Michael Meskes <meskes@postgresql.org>
Strange bahaviour
Michael Meskes <meskes@postgresql.org> writes:
> CREATE FUNCTION footest1(varchar(100)) RETURNS varchar(100) AS '
>         DECLARE
>                 login varchar(100);
>         BEGIN
>                 SELECT INTO login login FROM foo LIMIT 1;
>                 RETURN login;
>         END;
> ' LANGUAGE 'plpgsql';

> The first select returns NULL while the second correctly returns 'abc'.

The NULL is perfectly correct: that's the initial value of the plpgsql
variable.  The above is essentially the same as saying
	login := login;
It is not "incorrect".

> Now I can guess what happens but I wonder if this is the desired
> behaviour.

Certainly, unless you'd like to disable all use of plpgsql variables in
SQL queries.  plpgsql has no way of guessing that "login" in the above
query wasn't intended to reference its variable.  Either choose a
different variable name, or qualify the query-variable reference
(eg, foo.login).

			regards, tom lane

Mon, 12 Aug 2002 09:40:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Strange bahaviour 
On Mon, Aug 12, 2002 at 09:40:12AM -0400, Tom Lane wrote:
> The NULL is perfectly correct: that's the initial value of the plpgsql
> variable.  The above is essentially the same as saying
> 	login := login;
> It is not "incorrect".

That's exactly what I thought is the reason. I just wonder if there's a
way to make this kind of stuff more obvious for instance by using :login
for the variable as with embedded SQL. IMO the actual behaviour, while
of course correct, is a little bit confusing.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Mon, 12 Aug 2002 16:00:30 +0200
Michael Meskes <meskes@postgresql.org>
Re: Strange bahaviour
Just go with tradition and label all of your variables with a
v_<varname>.  Never use columns or tablenames prefixed with a v_.

It's a quick way for determining what is what.  Forcing use of a prefix 
in some places and not others would not be a nice thing -- especially as
the core takes on more and more abilities of plpgsql.


On Mon, 2002-08-12 at 10:00, Michael Meskes wrote:
> On Mon, Aug 12, 2002 at 09:40:12AM -0400, Tom Lane wrote:
> > The NULL is perfectly correct: that's the initial value of the plpgsql
> > variable.  The above is essentially the same as saying
> > 	login := login;
> > It is not "incorrect".
> 
> That's exactly what I thought is the reason. I just wonder if there's a
> way to make this kind of stuff more obvious for instance by using :login
> for the variable as with embedded SQL. IMO the actual behaviour, while
> of course correct, is a little bit confusing.




12 Aug 2002 10:24:24 -0400
Rod Taylor <rbt@zort.ca>
Re: Strange bahaviour