Re: plpgsql - accessing fields of record type

On Sun, Sep 26, 2004 at 06:15:57PM +0200, Sz?lka Tam?s wrote:

> I have a table with field named "XY_1", "XY_2", ... "XY_255".
> I want to access the values in the table from a plpgsql function somehow
> in a loop with the iterative variable. Can I address the appropriate
> ''XY_'' || loopvariable field with the help of the loop variable?

Could you tell us a little more about the application? I'm wondering
if the table layout is really the best way to organize the data.

> I have thought of an alternative solution: can plpgsql convert a record
> type into array? (Then I could address the field value with the nth
> element of the array)

You could use an array constructor if none of the fields could
possibly be NULL (elements of an array can't be NULL). The code
would look something like this:

FOR r IN SELECT ARRAY[XY_1, XY_2, ... XY_255] AS a FROM tbl LOOP
FOR i IN 1 .. 255 LOOP
-- do something with r.a[i]
END LOOP;
END LOOP;

If you didn't want to type all the field names, you could construct
the query string with a loop and use FOR-IN-EXECUTE, but then you'd
lose the advantage of a prepared execution plan on subsequent calls
to the function, not to mention the time spent constructing the
query string on each call.

As I suggested above, perhaps there's a better way to organize the
data.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/