[SQL] 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_'' || loopvariablefield 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: [SQL] 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_'' || loopvariablefield 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
Re: [SQL] COUNT(*) to find records which have a certain number of
Am 20.09.2004 18:19 schrieb T E Schmitz: > I figured it eventually. (The only thing I don't know is where to put > the ORDER BY.) > > >>I want to select only those BRAND/MODEL combinations, where the MODEL >>has more than one TYPE, but only where one of those has TYPE_NAME='xyz'. >>I am not interested in MODELs with multiple TYPEs where none of them are >>called 'xyz'. > > Try this: select b.brand_name,m.model_name,count(*) from model m inner join brand b on m.brand_fk=b.brand_pk inner join type t on m.model_pk=t.model_fk where t.type_name='xyz' group by b.brand_name,m.model_name having count(m.model_name)>0; Greetings, Martin -- Martin Knipper www : http://www.mk-os.de Mail : [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
