X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E19D49DCCB8 for ; Mon, 27 Mar 2006 11:05:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84924-03 for ; Mon, 27 Mar 2006 11:05:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 87E079DCBC1 for ; Mon, 27 Mar 2006 11:05:30 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 993C356427; Mon, 27 Mar 2006 09:05:34 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Mon, 27 Mar 2006 09:05:34 -0600 Date: Mon, 27 Mar 2006 09:05:34 -0600 From: "Jim C. Nasby" To: Tom Lane Cc: Wiebe Cazemier , pgsql-sql@postgresql.org Subject: Re: unique names in variables and columns in plsql functions Message-ID: <20060327150534.GX80726@pervasive.com> References: <4427F7D3.3000103@gmail.com> <14263.1143471744@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <14263.1143471744@sss.pgh.pa.us> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060327:tgl@sss.pgh.pa.us::vyTzumQdtyyWmAG7:00000000000000000000 0000000000000000000000001anE X-Hashcash: 1:20:060327:halfgaar@gmail.com::lFLqOJ9DVDeN0FoO:0000000000000000000 0000000000000000000000001TJd X-Hashcash: 1:20:060327:pgsql-sql@postgresql.org::TvPn+cCbJge0w7tV:0000000000000 0000000000000000000000000PYR X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/428 X-Sequence-Number: 24774 On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote: > Wiebe Cazemier writes: > > DECLARE > > provider_id INTEGER; > > BEGIN > > provider_id := (SELECT provider_id FROM investment_products WHERE id = > > my_new.investment_product_id); > > END; > > > After a lot of trouble, I found out this line doesn't work correctly > > with the variable name as it is. It doesn't give an error or anything, > > it just retrieves some wrong value (probably NULL). > > It'll retrieve whatever the current value of the plpgsql variable > provider_id is. plpgsql always assumes that ambiguous names refer > to its variables (indeed, it isn't even directly aware that there's > any possible ambiguity here). > > > I was somewhat surprised to discover this. Can't Postgres determine that > > the provider_id in the SELECT statement is not the same one as the variable? > > How and why would it determine that? In general it's perfectly normal > to use plpgsql variable values in SQL commands. I don't think it'd make > the system more usable if the parser tried to apply a heuristic rule > about some occurrences being meant as variable references and other ones > not. If the rule ever got it wrong, it'd be even more confusing. BTW, I believe SELECT investment_products.provider_id would work here, but I'm too lazy to test that theory out. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461