Re: unique names in variables and columns in plsql functions

Wiebe Cazemier <halfgaar(at)gmail(dot)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