Re: proposal: plpgsql - iteration over fields of rec or row variable

On Mon, Nov 8, 2010 at 2:29 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2010/11/8 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> FOR varname OVER [row | rec variable]
>>> LOOP
>>>    {{body}}
>>> END LOOP
>>
>>> this syntax isn't final. The real type of control variable is
>>> specified in runtime and can be changed between iterations.
>>
>> If you don't know the type or even the name of the field that varname
>> currently represents, how is the loop body going to do anything useful?
>>
>
> you don't know type or name in validation time. But you don't need to
> know it. Name is useless because you access to field via control
> variable and type is known in runtime - outer loop's body is
> unspecified, but inside loop's body is known. It's analogy to
> functions with polymorphic parameters. Outside and in validation time
> is parameter type unknown. Inside function in runtime parameter type
> is known. I though about it some minutes more, and probably we can do
> it without this baroque statement
>
>>> This variable should be
>>> writeable - so we are able to change any field of record.
>>
>> And that is just plain horrid.  The loop variable is a separate
>> variable, not a modifiable alias for a field, in every existing form
>> of plpgsql loop.
>>
>
> this variable can be like we need - this is automatic declared
> variable - we can define a new DTYPE flag, so we are able to control a
> assign to this variable - we can block a writing or we can to forward
> changes to outer variable. If we can do rowvar.field = some or
> recvar.field = some, then we are able to do dynamically too.
>
>> The idea of multiple instances of the loop body code seems like a mess
>> anyway.  I think this is basically hacking plpgsql beyond recognition
>> to solve problems that are better solved in plperl or pltcl.
>
> I think about it long time. There are a two basic issues: a) one plan
> for one assign statement b) dynamic selection of some record's field.
> Both points cannot be solved now, because every field can have  a
> different type. So this syntax changing it. For every field we have a
> special limited space, so we can work locally with "mutable" plans,
> because the plans are fixed in one iteration. I am sure, so @a can be
> solved relative simply without FOR OVER or similar construct. But @b
> is more difficult - you can do it just on SQL level, but it need a
> generating path in plan for every field in record.
>
> I know so LOOP OVER is relative heavy, but it decrease a necessary
> changes in SQL planner to zero
>
> One note - the idea of multiple instances of stored plans inside
> PLpgSQL expr isn't far to your proposal of solution for bad plans?
>
> I am open to any ideas. Now I am a searching a possible way. With last
> change in plperl it is relative simple to iterate over row or record -
> and with possible a access to type descriptor, the iteration can be
> relative simple. But I see a main disadvantage: any value must be one
> or more times serialized or deserialized to text - and plperl must be
> enabled.

Most cases of this feature are for dealing with new/old from trigger
function right? Why not build a complete new plan for each specific
trigger that invokes the function, along with some magic values like
(TG_FIELDNAMES -> text[]) that could be iterated for the mojo. Not
sure how you get direct type assignment to variable but it could
probably be worked out.

merlin