Declaring multidimensional arrays in pl/pgsql

On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff@gmail.com> wrote:

According to the docs it seems that only way would be to declare it as
something like :
myArray := ARRAY[[1,2], [3,4], [5,6]];

You can declare arbitrary-sized, n-dimensional arrays:
...
DECLARE
myArray integer[][]; -- two-dimensional integer array
BEGIN
...
END;
...

See:
http://www.postgresql.org/docs/8.1/static/arrays.html

I wrote:

You can declare arbitrary-sized, n-dimensional arrays:

Sorry, I re-read your post.

You want to programatically define the array dimensions depending on
function arguments.

You could try building a string, then casting to the correct array
type (not tested).

On Thu, 29 Nov 2007 18:11:22 +0400, Rodrigo De León <rdeleonp@gmail.com>
wrote:

On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff@gmail.com> wrote:

According to the docs it seems that only way would be to declare it as
something like :
myArray := ARRAY[[1,2], [3,4], [5,6]];

You can declare arbitrary-sized, n-dimensional arrays:
...
DECLARE
myArray integer[][]; -- two-dimensional integer array
BEGIN
...
END;
...

See:
http://www.postgresql.org/docs/8.1/static/arrays.html

I can. But unfortunately :

create or replace function testfunc()
returns setof record as $$
DECLARE
myArray int[][];
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
RAISE NOTICE '% %', i, j;
myArray[i][j] := 1;

END LOOP;
END LOOP;

RETURN;
END
$$ language plpgsql;

ponline=# select testfunc();
NOTICE: 1 1
NOTICE: 1 2
ERROR: invalid array subscripts
КОНТЕКСТ: PL/pgSQL function "testfunc" line 7 at assignment

2-dimensional arrays do not grow like 1-dimensional do (it says so in the
docs).
The initial array is 1x1 size. I suppose I'm stuck with emulating 2-dim
arrays through
1-dim arrays because I also need them to grow later.

On Thu, 29 Nov 2007 19:21:03 +0400, Pavel Stehule
<pavel.stehule@gmail.com> wrote:

Hello

arrays in PostgreSQL have to be regular allways. And before 8.3 array
cannot contais NULL, so you cannot simpl resize two dim array :(. But
your functions doesn't work in 8.3. too. So you can

a) use 1D array and access to array like myarray[10*(n1-1)+n2]

b) init array with string like

create or replace function testfunc()
returns void as $$
DECLARE
myArray int[][];
BEGIN
myArray := ('{'||array_to_string(array(select
'{0,0,0,0,0,0,0,0,0,0}'::text from
generate_series(1,10)),',')||'}')::int[][];
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
RAISE NOTICE '% %', i, j;
myArray[i][j] := 1;
END LOOP;
END LOOP;
RETURN;
END
$$ language plpgsql;

Thanks for the info, but running the above gives me that :

ponline=# select testfunc();
ERROR: cannot cast type text to integer[]
CONTEXT: SQL statement "SELECT ('{'||array_to_string(array(select
'{0,0,0,0,0,0,0,0,0,0}'::text from
generate_series(1,10)),',')||'}')::int[][]"
PL/pgSQL function "testfunc" line 4 at assignment

I think 8.1 does not have text -> int[] cast available. I think I'm stuck
with option a.

On Thu, 29 Nov 2007 21:15:50 +0400, Rodrigo De León <rdeleonp@gmail.com>
wrote:

On Nov 29, 2007 9:33 AM, Max Zorloff <zorloff@gmail.com> wrote:

I don't think that works.

ponline=# select ('{1,2,3}'::text)::int[];
ERROR: cannot cast type text to integer[]

Can you try:

select ('{1,2,3}'::unknown)::int[];

Thanks, that works fine with 2-dim arrays too.