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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: plpgsql - iteration over fields of rec or row variable
Date: 2010-11-08 18:24:14
Message-ID: AANLkTintS=WCAD_sd=GAgO=74A-jS9gokx95zGBoFCJf@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I try to take problem of iteration over ROW or RECORD variable from a
different perspective. I would to design a solution where isn't
necessary a conversion from binary value to text (this is a
disadvantage of hstore based solution). This mean so we have to have a
special instance of loop's body for every field of record (for every
field with different type then other field). Can we do it? Yes, we can
- we can use a similar access like polymorphic parameters - just used
not on function level, but on block level. We can iterate of record's
fields and for any distinct type we can do new instance of block
(loop's body) with new instances of included plans. I am thinking
about following syntax:

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. But it
isn't problem, because for any unique data type we will have a
separate instance of {{body}}. control variable with name 'varname' is
redeclared for every iteration of cycle. This variable should be
writeable - so we are able to change any field of record. We can
define a automatic variable FIELDNAME that holds a name of processed
field.

so: sum over some row or rec var can be done with code:

CREATE rectype AS (x int, y int, f float);

DECLARE revar rectype;
BEGIN
FOR f OVER recvar
LOOP
sum := sum + f;
END LOOP;
...

or
FOR f OVER recvar
LOOP
IF fieldname LIKE 'value%' THEN
sum := sum + f;
END IF;
END LOOP;

some other examples:

FOR f OVER recvar
LOOP
RAISE NOTICE '% => %', fieldname, f;
END LOOP;

FOR f OVER recvar
LOOP
IF fieldname LIKE 'value%' THEN
f := 0;
END IF;
END LOOP;

What are you thinking of this proposal?

Regards

Pavel Stehule

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-11-08 18:30:58 Re: How to share the result data of separated plan
Previous Message Tom Lane 2010-11-08 18:19:54 Re: UNION ALL has higher cost than inheritance