Skip site navigation (1) Skip section navigation (2)

Re: plpgsql - accessing fields of record type

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Sz?lka Tam?s <rontombontom(at)freestart(dot)hu>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: plpgsql - accessing fields of record type
Date: 2004-09-26 18:33:43
Message-ID: 20040926183343.GA64575@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-sql
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/

In response to

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-09-27 00:21:13
Subject: Re: where clause on a left outer join
Previous:From: Szálka TamásDate: 2004-09-26 16:15:57
Subject: plpgsql - accessing fields of record type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group