Message-ID: <414FB192.5060100@joeconway.com>
Date: Mon, 20 Sep 2004 21:44:02 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803
MIME-Version: 1.0
To: sergio.fantinel@lnl.infn.it
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL multidimension (matrix) array in function
References: <4149C90D.60806@lnl.infn.it>
In-Reply-To: <4149C90D.60806@lnl.infn.it>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Sergio Fantinel wrote:
> I found how to use, inside a PL/pgSQL function, a two-dimensions array 
> (matrix).
> There is a limitation: the number of the 'columns' of the matrix is 
> fixed at declaration time (in DECLARE section) and you need to manually 
> initialize all the elements in the first 'row' of the matrix.

You should use '{}' to initialize the array to empty. See below for an 
example:

CREATE OR REPLACE FUNCTION testarray (integer, integer) RETURNS SETOF 
integer[] AS'
DECLARE
   n alias for $1;    -- number of rows is passed as argument
   i INTEGER;
   j integer;
   k alias for $2;          -- matrix columns number
   a integer[];
begin
   for i in 1..n loop
    a := ''{}'';               -- create empty array
    for j in 1..k loop
      a := a || i;
      return next a;
    end loop;
   end loop;
   return;
end;
'LANGUAGE 'plpgsql';

regression=# select * from testarray(2,3);
  testarray
-----------
  {1}
  {1,1}
  {1,1,1}
  {2}
  {2,2}
  {2,2,2}
(6 rows)

HTH,

Joe