In article <441AE917.9010008@xxxxxxxxxx>, ken@xxxxxxxxxx says... > SunWuKung wrote: > > >In article <441AD636.6090009@xxxxxxxxxxxx>, dev@xxxxxxxxxxxx says... > > > > > >>SunWuKung wrote: > >> > >> > >>>Select Into max_option parameter_value From parameters Where methodid= > >>>999 And parameter_name='max_option' > >>> > >>>and so on for each parameter. > >>> > >>> > What you are trying to do is a transpose, taking a column of values and > turning it into a row. > > You transpose columns to rows by doing a JOIN of some flavor or another > (in this case a cross-join in which the filters bring us down to one row > per table). The reverse operation is done with UNIONs. > > This code has not been tested, but it should get the idea across. It > should also be fairly easy to generate in the client since it is systematic: > > SELECT into parm1, parm2, parm3 > x1.parameter_value, x2.parameter_value, x3.parameter_value > FROM parameters x1,parameters x2, parameters x3 > WHERE x1.parameter_name = 'USA Patriot ACT' > AND x2.parameter_name = 'Is not constitutional' > AND x3.paremter_name = 'IMHO' > AND x1.methodid=999 > AND x2.methodid=999 > AND x3.methodid=999 > > Hope it works! > > > > >>>Is there a way to get all these parameters into one variable in pgsql - > >>>eg. Select Into paramarray(param_name, param_value) parameter_name, > >>>parameter_value Where methodid=999 - and refer to their values in a > >>>simple way like param_array.max_option ? > >>> > >>> > >>Have a look at using a variable of type RECORD. See the plpgsql > >>documentation for examples. > >> > >> > >> > >> Thanks for this, I am sure this works, but this is basically the same as writing a Select for each parameter - which is what I was trying to avoid. Balázs
