Thread: pgsql variables from records

I have a table in which I am storing parameters that I would like to use
as variables in a pgsql procedure.

Currently I find no other way to refer to these than to assign each
record to a variable by a separate query like this:

Declare
max_option integer;

Select Into max_option parameter_value From parameters Where methodid=
999 And parameter_name='max_option'

and so on for each parameter.

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 ?

Thanks for the help.
Balázs

SunWuKung wrote:
> Select Into max_option parameter_value From parameters Where methodid=
> 999 And parameter_name='max_option'
>
> and so on for each parameter.
>
> 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.

--
   Richard Huxton
   Archonet Ltd

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.
> >
> > 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

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.

   

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@postgresql.org so that your      message can get through to the mailing list cleanly 

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