"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
