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 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
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.
Show quoted text
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.