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 237159DCD21 for ; Mon, 27 Mar 2006 10:48:18 -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 79385-06 for ; Mon, 27 Mar 2006 10:48:22 -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 D0AD79DCBC6 for ; Mon, 27 Mar 2006 10:48:15 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 189E956423; Mon, 27 Mar 2006 08:48:05 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Mon, 27 Mar 2006 08:48:04 -0600 Date: Mon, 27 Mar 2006 08:48:04 -0600 From: "Jim C. Nasby" To: Wiebe Cazemier Cc: pgsql-sql@postgresql.org Subject: Re: unique names in variables and columns in plsql functions Message-ID: <20060327144804.GV80726@pervasive.com> References: <4427F7D3.3000103@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4427F7D3.3000103@gmail.com> 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:halfgaar@gmail.com::G0cRQJ9EjUbT3f2n:0000000000000000000 0000000000000000000000001Gjk X-Hashcash: 1:20:060327:pgsql-sql@postgresql.org::qtIqtC7qFBzD4FD8:0000000000000 00000000000000000000000017C8 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/425 X-Sequence-Number: 24771 On Mon, Mar 27, 2006 at 04:33:55PM +0200, Wiebe Cazemier wrote: > Hi, > > In a plpgsl function, consider the following excerpt: > > 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). When I change the > variable name to anything other than "provider_id", it works OK. > > 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? Sadly, overloading variable names between plpgsql and SQL is *highly* problematic. Because of this I *always* prefix plpgsql variables with something, such as p_ for parameters and v_ for general variables. -- 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