pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL: possible parsing or documentation bug?
2+ messages / 2 participants
[nested] [flat]
* PL/pgSQL: possible parsing or documentation bug?
@ 2001-01-31 21:39 Albert REINER <areiner@tph.tuwien.ac.at>
2001-02-03 22:15 ` Re: PL/pgSQL: possible parsing or documentation bug? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Albert REINER @ 2001-01-31 21:39 UTC (permalink / raw)
To: pgsql-sql
Saluton,
plpgsql seems to get confused when using variable names that coincide
with, e.g., column names:
create Function IdOfPossiblyNewAuthor(text) returns int4 as '
declare
id int4;
begin
select id into id from author where name = $1;
raise notice ''ID found: %'', id;
if id is null then
insert into author (name) values ($1);
select currval(''author_id_seq'') into id;
raise debug ''Author inserted. ID: %'', id;
end if;
return id;
end;
' language 'plpgsql' with (IsCachable);
Logically it is clear which "id" should be parsed as the variable,
which as author.id, but I have to use a different name to make this
work. I do not see any mention on this restriction of variable names
(the full extent of which I do not know) in the documentation (7.0.2).
Albert.
--
--------------------------------------------------------------------------
Albert Reiner <areiner@tph.tuwien.ac.at>
Deutsch * English * Esperanto * Latine
--------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PL/pgSQL: possible parsing or documentation bug?
2001-01-31 21:39 PL/pgSQL: possible parsing or documentation bug? Albert REINER <areiner@tph.tuwien.ac.at>
@ 2001-02-03 22:15 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2001-02-03 22:15 UTC (permalink / raw)
To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql
"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> create Function IdOfPossiblyNewAuthor(text) returns int4 as '
> declare
> id int4;
> begin
> select id into id from author where name = $1;
> raise notice ''ID found: %'', id;
> if id is null then
> insert into author (name) values ($1);
> select currval(''author_id_seq'') into id;
> raise debug ''Author inserted. ID: %'', id;
> end if;
> return id;
> end;
> ' language 'plpgsql' with (IsCachable);
> Logically it is clear which "id" should be parsed as the variable,
> which as author.id,
No, it is not so clear. Consider the following:
declare
x int4;
y int4;
begin
x := ...;
select x + f1 into y from tab1 where ...;
The intent here is clearly to find a value tab1.f1 in tab1 and then
add the local variable x to form a value for the local variable y.
In general plpgsql will try to match an unqualified name to a variable
before it will consider whether it might be a field name. If you don't
want that, qualify the field name:
select author.id into id from author where name = $1;
Feel free to submit documentation updates to make this clearer...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-02-03 22:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-31 21:39 PL/pgSQL: possible parsing or documentation bug? Albert REINER <areiner@tph.tuwien.ac.at>
2001-02-03 22:15 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox