Thread: Declaring arrays in plpgsql functions

Declaring arrays in plpgsql functions

From
Daniel Lundin
Date:
I'm trying to convert rows from a table into a two dimensional array, and
thought I could do so in a plpgsql function, but I can't declare the return
variable as an array:

create function reservationIds(int4) returns text[][] as '   declare     paramOriginalResNr        alias for $1;
     rs                        record;
     id_src                    text[][];
   begin     for rs in       select         src,         id       from         ReservationId       where
originalResNr= paramOriginalResNr     loop       text[array_dim(text) + 1][0] := rs.src;       text[array_dim(text) +
1][1]:= rs.id;     end loop;
 
     return id_src;   end; '
language 'plpgsql';

, but when I select the function the parser complains: 'ERROR:  parse error at
or near "["'. It seems to be the id_src declaration that's not correct; the
returns text[][] works.

I've tried text[2][30] to the same effect.

/Daniel



Re: Declaring arrays in plpgsql functions

From
Tom Lane
Date:
Daniel Lundin <daniel@helena-daniel.se> writes:
> I'm trying to convert rows from a table into a two dimensional array, and
> thought I could do so in a plpgsql function, but I can't declare the return
> variable as an array:

The declaration is fine (at least it works for me, in 7.2).  The trouble
is with:

>         text[array_dim(text) + 1][0] := rs.src;
>         text[array_dim(text) + 1][1] := rs.id;

plpgsql doesn't support assigning to array elements :-(.  (Even if it
did, you couldn't use array_dims() like that --- array_dims() returns
a string.)
        regards, tom lane


Re: Declaring arrays in plpgsql functions

From
Tom Lane
Date:
I wrote:
> plpgsql doesn't support assigning to array elements :-(.

Drat, forgot a point I intended to make:

You might have better luck working in pltcl; it has better support for
arrays than plpgsql does.
        regards, tom lane


Re: Declaring arrays in plpgsql functions

From
Daniel Lundin
Date:
On mån, mar 25, 2002 at 02:41:49 -0500, Tom Lane wrote:
> I wrote:
> > plpgsql doesn't support assigning to array elements :-(.
> 
> Drat, forgot a point I intended to make:
> 
> You might have better luck working in pltcl; it has better support for
> arrays than plpgsql does.
> 
There's no chance with plperl, or have I understood it correctly, that plperl
doesn't support any way of doing selects?

>             regards, tom lane
>