Re: proposal: plpgsql - iteration over fields of rec or row variable - Mailing list pgsql-hackers

Hello

2010/11/8 Dmitriy Igrishin <dmitigr@gmail.com>:
> Hey Pavel, Tom, Merlin,
>
> As a user, I would like to work with records by using simple API:
>

>   -- Returns a number of key/values pairs of record.
>   nKeys_ integer := nRecordKeys(NEW);
>
>   -- Returns an i-th key.
>   key_i text := recordKey(NEW, i);
>
>   -- Returns an i-th value.
>   value1_ text := recordValueByIndex(NEW, i);
>
>   -- Returns an value by named key.
>   value2_ text := recordValueByName(NEW, "id");
>

some from your lines isn't problem now. You can convert all fields in
record to text and work with it. These functions is very simple. But
it isn't effective, because you convert fields to text and you can
lost a some information or you can get some different

like

intvar := 10.0 / 2.0; doesn't work in plpgsql, because there are casting via IO

Pavel



>
> The syntax with FOR .. LOOP for iteration across record keys
> seems to me not so elegant.
>
> 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.
>>
>> merlin
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>
> --
> // Dmitriy.
>
>
>