Re: [GENERAL] pgsql variables from records

Kenneth Downs Fri, 17 Mar 2006 08:52:35 -0800

SunWuKung wrote:

In article <[EMAIL PROTECTED]>, [email protected] 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 [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Re: [GENERAL] pgsql variables from records

Reply via email to