Обсуждение: 8.3.5 problem with plpgsql selecting into an array variable

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;
'

raf <raf@raf.org> writes:
> 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

Sorry, you can't do that ... per the fine manual, the INTO target can be
"a record variable, a row variable, or a comma-separated list of simple
variables and record/row fields".  Nothing there about array subscript
expressions.

            regards, tom lane

Tom Lane wrote:

> raf <raf@raf.org> writes:
> > 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
>
> Sorry, you can't do that ... per the fine manual, the INTO target can be
> "a record variable, a row variable, or a comma-separated list of simple
> variables and record/row fields".  Nothing there about array subscript
> expressions.
>
>             regards, tom lane

hi tom,

i had read the manual and the end of my original message
said what you've just said followed by a request for this
non-orthogonality in plpgsql to be fixed.

i'm just repeating myself in case you responded without
reading that part of the message (busy man that you are).

no doubt it's a low priority thing but not allowing array
elements here is hardly a desirable thing.

cheers,
raf