Re: Declaring multidimensional arrays in pl/pgsql

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.