Обсуждение: pgsql variables from records

SunWuKung <Balazs.Klein@axelero.hu> wrote:
>
> 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:

I'm not sure if you are talking about referenceing the individual
columns, or a set of rows.

For the first case

DECLARE
   parameters  tp_method1_params;

BEGIN
....

   parameters := (SELECT param1, ...paramn) FROM paramtable;

   SELECT method(param1, ...paramn);

END;

Or you could declare the method to accapt the record as its input
parameter.

For the second case, use an array.  I just learned how to do that
on this list a couple of days ago.


DECLARE
   paramarray  tp_method_params[];

BEGIN
....

   paramarray := ARRAY(SELECT ....);

END;

I hope this answers the question you were asking.  {-;

-karl

> 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

In article <20060317112123.A11005@kipshouse.org>, karly@kipshouse.org
says...
> SunWuKung <Balazs.Klein@axelero.hu> wrote:
> >
> > 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:
>
> I'm not sure if you are talking about referenceing the individual
> columns, or a set of rows.
>
> For the first case
>
> DECLARE
>    parameters  tp_method1_params;
>
> BEGIN
> ....
>
>    parameters := (SELECT param1, ...paramn) FROM paramtable;
>
>    SELECT method(param1, ...paramn);
>
> END;
>
> Or you could declare the method to accapt the record as its input
> parameter.
>
> For the second case, use an array.  I just learned how to do that
> on this list a couple of days ago.
>
>
> DECLARE
>    paramarray  tp_method_params[];
>
> BEGIN
> ....
>
>    paramarray := ARRAY(SELECT ....);
>
> END;
>
> I hope this answers the question you were asking.  {-;
>
> -karl
>
> > Declare
> > max_option integer;
> >
> > Select Into max_option parameter_value From parameters Where methodid=

Its the second case.
Yes, arrays would be good, however I would need to refer to each value
by its subscript number eg.
Select * From sometable Where id=paramarray[1]
however in my case parameters have no logical order so I would like to
refer to them by their id, like perl hashes eg.
Select * From sometable Where id=paramarray{'max_option'}
(I know there is plperl but I've never tried that and I wouldn't want to
learn it just for this.)
Maybe this can be done by creating a type and an operator for it - I
don't know I have never tried those either.