Re: pgsql variables from records

SunWuKung wrote:

>In article <441AD636(dot)6090009(at)archonet(dot)com>, dev(at)archonet(dot)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.
>>
>>
>>
>>
>This sounds very promising, but I have looked at the RECORD variable
>type, and found no example that would show how I could use it the way I
>described.
>In order to get the value of rec.max_option I would first need to
>dynamically create a record variable in which all the param_name values
>of the original recordset are columns and contain the corresponding
>param_value. I found no example how to do this - could you direct me to
>one?
>
>Thanks.
>Balázs
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>