plpgsql - accessing fields of record type

Hi!

I'd like to ask whether it is possible to access fields of record type
variable in plpgsql in the following way:

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?
If I can, what is the syntax of this? Sorry, I've read through the
documentation, but haven't find any solution.

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)

Thanks for your help

tomasz

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster