Re: Passing array to PL/SQL and looping

Ian Barwick <barwick@gmx.net> writes:
> On Friday 27 September 2002 18:04, Josh Berkus wrote:
>> Currently, if you want to use an array, it has to be passed as a
>> parameter, or come from an external table.   You cannot declare an
>> Array data type.  Annoying, really.

> If I replace the return_array allocations in the above example with this 
> line:
>   return_array := ''{ ''''test'''', ''''test 1'''', ''''test 2''''}'';
> it _seems_ to work as expected, at least in 7.3b1.

Yes, operations that work on whole-array values work fine in plpgsql.
It's accesses to array elements that aren't supported well.  The main
case that seems to be missing in current sources is exactly assignment
to an array element, ie foo[n] := something.

More generally --- and this is really a shortcoming in our SQL language,
not specifically in plpgsql --- there isn't any way to construct an
array value as an expression result.  I can imagine writing something
likeSELECT CAST(1,2,3 AS int[3]);
but we've got nothing like that at the moment.  (If you don't see the
point of this as compared toSELECT CAST('{1,2,3}' AS int[3]);
then think about replacing the 1, 2, and 3 by arbitrary integer
expressions.)
        regards, tom lane