pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/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>
  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-02-03 22:15  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Albert REINER <areiner@tph.tuwien.ac.at>
  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