Thread: Strange bahaviour

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!

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 sayinglogin := 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

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!

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.