8.3.5 problem with plpgsql selecting into an array variable

Поиск
Список
Период
Сортировка
От raf
Тема 8.3.5 problem with plpgsql selecting into an array variable
Дата
Msg-id 20090406034919.GA821@raf.org
обсуждение исходный текст
Ответы Re: 8.3.5 problem with plpgsql selecting into an array variable  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
hi,

postgresql-8.3.5

i'm seeing the following unexpected syntax error trying to
select into a local array variable element.

it seems that this:

  select sum(expr[1]), sum(expr[2]) into var[1], var[2] from...

is being turned into this:

  select sum(expr[1]), sum(expr[2])[1], $1[2] from...

instead of this:

  select sum(expr[1]), sum(expr[2]) into $1[1], $1[2] from...

what am i doing wrong?

ah, section 38.5.3 of the postgres documentation
states that the values in a single row select can
be selected into a record variable, a row variable,
or list of scalar variables.

does this mean that the elements of an array aren't
considered to be scalar variables (even though they
are scalar and their values do vary)? that's a pity.

consider this as a request to add "scalar elements of
array variables" to the list of valid targets of a
single row select.

oh well. time to write some ugly code...

cheers,
raf

------- full error message --------------------------------------------

error 'ERROR:  syntax error at or near "["
LINE 1: ...sum(p.balance_period[1]), sum(p.balance_period[2])[1],  $1 [...
                                                             ^
QUERY:  select sum(p.balance_period[1]), sum(p.balance_period[2])[1],  $1 [2] from payee p where p.pay_group_id =  $2
andp.last_paid_period =  $3  return 'OK' 
CONTEXT:  SQL statement in PL/PgSQL function "huh" near line 17
' in 'create or replace function huh(pay_group_id integer)
returns text volatile language plpgsql as $$
declare
        pay_group_rec pay_group;
        balance decimal(10,2)[];
begin
        select g.* into pay_group_rec from pay_group g where g.id = pay_group_id;

        select
                sum(p.balance_period[1]),
                sum(p.balance_period[2])
        into
                balance[1],
                balance[2]
        from
                payee p
        where
                p.pay_group_id = pay_group_id and
                p.last_paid_period = pay_group_rec.pay_period

        return 'OK';
end
$$
security definer
set search_path = public, pg_temp;
revoke all on function huh(pay_group_id integer) from public;
grant execute on function huh(pay_group_id integer) to staff;
'


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: copy from with trigger
Следующее
От: Tom Lane
Дата:
Сообщение: Re: 8.3.5 problem with plpgsql selecting into an array variable