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