Обсуждение: unique names in variables and columns in plsql functions
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?
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
Wiebe Cazemier <halfgaar@gmail.com> 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
On Mon, Mar 27, 2006 at 10:02:24AM -0500, Tom Lane wrote: > Wiebe Cazemier <halfgaar@gmail.com> 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
(Whoops, pressed wrong reply button. Here it is correctly this time.) On 03/27/06 16:48, Jim C. Nasby wrote: > >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. > > Hmm. Well, at least now I'm aware of it. Perhaps I will do something similair to prefixes from now on, it would seem to be good practice.
(Whoops, pressed wrong reply button. Here it is correctly this time.) On 03/27/06 17:02, Tom Lane wrote: >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). > > That's why I suspected it would be NULL, since provider_id wasn't initialised yet. > >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. > I guess I'm looking at it too much as a human. I said "SELECT FROM" so, it guess I assumed it would be clear enough which one it had to use. But, now that I'm thinking about it some more, I agree. However, a fatal error would also have been welcome.