8.3.5 problem with plpgsql selecting into an array variable - Mailing list pgsql-general

From raf
Subject 8.3.5 problem with plpgsql selecting into an array variable
Date
Msg-id 20090406034919.GA821@raf.org
Whole thread Raw
Responses Re: 8.3.5 problem with plpgsql selecting into an array variable
List 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 by date:

Previous
From: Tom Lane
Date:
Subject: Re: copy from with trigger
Next
From: Tom Lane
Date:
Subject: Re: 8.3.5 problem with plpgsql selecting into an array variable