pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
plpgsql - accessing fields of record type 2+ messages / 2 participants [nested] [flat]
* plpgsql - accessing fields of record type @ 2004-09-26 16:15 Szálka Tamás <rontombontom@freestart.hu> 0 siblings, 1 reply; 2+ messages in thread From: Szálka Tamás @ 2004-09-26 16:15 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: plpgsql - accessing fields of record type @ 2004-09-26 18:33 Michael Fuhr <mike@fuhr.org> parent: Szálka Tamás <rontombontom@freestart.hu> 0 siblings, 0 replies; 2+ messages in thread From: Michael Fuhr @ 2004-09-26 18:33 UTC (permalink / raw) To: Sz?lka Tam?s <rontombontom@freestart.hu>; +Cc: 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/ ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-26 18:33 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-09-26 16:15 plpgsql - accessing fields of record type Szálka Tamás <rontombontom@freestart.hu> 2004-09-26 18:33 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox