On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote: > Wiebe Cazemier <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
