Re: Some notes about Param handling with "Oracle style" plpgsql variables

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Nov 2, 2009 at 11:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Another interesting property
>> of this approach is that it'd fix the longstanding user complaint
>> that constructions like
>> if (TG_OP = 'INSERT' and NEW.foo = 'bar') ...
>> fail prematurely. The executor would never demand the value
>> of NEW.foo, and thus not fail, if TG_OP isn't INSERT.

> I don't really know enough to comment on the best way to go about this
> project overall, but fixing this would be incredibly nice, if it can
> be done without too much damage.

After further reflection, there's a little more here than meets the eye.
We can make it work as above for constructs that execute indivisibly
from the point of view of a plpgsql function, like simple expressions.
But there are also time-extended executions, like cursors and FOR-loop
queries. What happens if you do something like

declare x int;
...
for r in select * from tab where id = x loop ...

and change x inside the loop?

Currently the code guarantees that these queries are run using the
values that plpgsql variables had at the opening of the cursor or start
of the for-loop. I think it would be a really bad idea to let it behave
any differently --- even if it were rational to do something different,
can you imagine trying to find bugs caused by such a change in functions
that used to work? But that means we have to evaluate and copy the
values of all variables that such a query *could* reference, even if it
then fails to touch them at runtime.

This doesn't seem like a fatal objection to me, but it's worth
mentioning that the improvement will only apply in some contexts.

regards, tom lane