Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f13MH5x20320 for ; Sat, 3 Feb 2001 17:17:05 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f13MFp904320; Sat, 3 Feb 2001 17:15:51 -0500 (EST) To: "Albert REINER" cc: PostgreSQL-SQL Subject: Re: PL/pgSQL: possible parsing or documentation bug? In-reply-to: <20010131223906.A1337@frithjof> References: <20010131223906.A1337@frithjof> Comments: In-reply-to "Albert REINER" message dated "Wed, 31 Jan 2001 22:39:06 +0100" Date: Sat, 03 Feb 2001 17:15:51 -0500 Message-ID: <4317.981238551@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200102/25 X-Sequence-Number: 1193 "Albert REINER" 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