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

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.

Regards

Pavel Stehule


>
>                        regards, tom lane
>