pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL multidimension (matrix) array in function
2+ messages / 2 participants
[nested] [flat]
* PL/pgSQL multidimension (matrix) array in function
@ 2004-09-16 17:10 Sergio Fantinel <sergio.fantinel@lnl.infn.it>
0 siblings, 1 reply; 2+ messages in thread
From: Sergio Fantinel @ 2004-09-16 17:10 UTC (permalink / raw)
To: pgsql-sql
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
Cheers
Sergio
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 next null;
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
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PL/pgSQL multidimension (matrix) array in function
@ 2004-09-21 04:44 Joe Conway <mail@joeconway.com>
parent: Sergio Fantinel <sergio.fantinel@lnl.infn.it>
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2004-09-21 04:44 UTC (permalink / raw)
To: sergio.fantinel@lnl.infn.it; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-21 04:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-16 17:10 PL/pgSQL multidimension (matrix) array in function Sergio Fantinel <sergio.fantinel@lnl.infn.it>
2004-09-21 04:44 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox