Thread: PL/pgSQL multidimension (matrix) array in function

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.

The number of rows is unlimited and can be sized at runtime.

Here is the code that can help you.

If someone know how to manage an NxN array without limitations, please replay 
to this thread.

I'm using PostgreSQL 7.4.1 on RH7.3

CheersSergio

CREATE OR REPLACE FUNCTION "testarray" (integer) RETURNS SETOF integer AS'
DECLARE       n alias for $1;    -- number of rows is passed as argument       i INTEGER;       j integer;       k
INTEGER:= 3;            -- matrix columns number       b integer[] := array[0,0,0];    -- need it to initialize the
matrix!!      a integer[][] := array[[0,0,0]]; -- need it to initialize the matrix!!
 
begin
     for i in 1..n loop        -- the i loop can start obviously from 2 (the first 
row is already present...) but for our purpose here we use 1          a := array_cat(a,b);          for j in 1..k loop
           a[i][j] := i*j;          end loop;     end loop;     for i in 1..n loop
 
         return next null;         return next i;        -- need it to format in some way the output :)         return
nextnull;
 
         for j in 1..k loop             return next a[i][j];         end loop;
     end loop;     return;
end
'LANGUAGE 'plpgsql';


here is the output:

=> select * from testarray(8); testarray
-----------
         1
         1         2         3
         2
         2         4         6
         3
         3         6         9
         4
         4         8        12
         5
         5        10        15
         6
         6        12        18
         7
         7        14        21
         8
         8        16        24
(48 rows)


-- 
---------------------------------------------------------------------
Sergio Fantinel                           EGEE Project
---------------------------------------------------------------------
INFN - Lab. Naz. di Legnaro               phone: +39 049 8068 489
viale dell'Università n. 2,
35020 Legnaro (PD) ITALY                  sergio.fantinel@lnl.infn.it
---------------------------------------------------------------------

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