To: Ian Barwick <barwick@gmx.net>
Cc: "Josh Berkus" <josh@agliodbs.com>,
	Greg Johnson <gregj@interprose.com>, pgsql-sql@postgresql.org
Subject: Re: Passing array to PL/SQL and looping 
In-reply-to: <200209290025.06378.barwick@gmx.net> 
References: <web-1731886@davinci.ethosmedia.com>
	<200209290025.06378.barwick@gmx.net>
Comments: In-reply-to Ian Barwick <barwick@gmx.net>
	message dated "Sun, 29 Sep 2002 00:25:06 +0200"
Date: Sat, 28 Sep 2002 22:14:46 -0400
Message-ID: <6945.1033265686@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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
like
	SELECT 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 to
	SELECT CAST('{1,2,3}' AS int[3]);
then think about replacing the 1, 2, and 3 by arbitrary integer
expressions.)

			regards, tom lane