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 F1A579DD552 for ; Mon, 27 Mar 2006 11:02:33 -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 83579-07 for ; Mon, 27 Mar 2006 11:02:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id CED0A9DCDDA for ; Mon, 27 Mar 2006 11:02:22 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2RF2ORR014264; Mon, 27 Mar 2006 10:02:24 -0500 (EST) To: Wiebe Cazemier cc: pgsql-sql@postgresql.org Subject: Re: unique names in variables and columns in plsql functions In-reply-to: <4427F7D3.3000103@gmail.com> References: <4427F7D3.3000103@gmail.com> Comments: In-reply-to Wiebe Cazemier message dated "Mon, 27 Mar 2006 16:33:55 +0200" Date: Mon, 27 Mar 2006 10:02:24 -0500 Message-ID: <14263.1143471744@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/427 X-Sequence-Number: 24773 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. regards, tom lane