In article <441AE917.9010008@secdat.com>, ken@secdat.com says...
> SunWuKung wrote:
>
> >In article <441AD636.6090009@archonet.com>, dev@archonet.com 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