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

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");

and so on...

The syntax with FOR .. LOOP for iteration across record keys
seems to me not so elegant.

2010/11/8 Merlin Moncure <mmoncure(at)gmail(dot)com>

> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
// Dmitriy.