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

2010/11/8 Merlin Moncure <mmoncure@gmail.com>:
> On Mon, Nov 8, 2010 at 2:29 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2010/11/8 Tom Lane <tgl@sss.pgh.pa.us>:
>>> Pavel Stehule <pavel.stehule@gmail.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.

if I understand well - it's not too far to my idea - just you create
instance on function level? It is possible too. As disadvantages I
see:
a) you need some special syntax too
b) there is overhead with multiple function call
c) you have to manage some space for temporary values

Regards

Pavel Stehule
>
> merlin
>