Re: Multidimensional arrays in plpgsql

"Celia McInnis" <celia(at)drmath(dot)ca> writes:
> DECLARE
> myarray1 INT[2][2]:=array[[NULL,NULL],[NULL,NULL]];
> myarray2 INT[2][2]:=array[[NULL,NULL],[NULL,NULL]];

That's not going to work because we don't yet support NULL elements in
arrays. The two array variables will effectively be NULLs themselves,
not arrays containing nulls. (I think Joe Conway has made some noises
about fixing that for 8.1.)

> FOR rec IN EXECUTE q LOOP
> myarray1[i][j]:=rec.x;
> junk1:=myarray1[i][j];
> junk2:=rec.x;
> myarray2[i][j]:=junk2;
> RAISE NOTICE 'myarray1[%][%]=% myarray[%][%]=%',i,j,junk1,i,j,junk2;
> END LOOP;

You forgot to re-read myarray2[i][j], so the output of the RAISE doesn't
prove that myarray1 and myarray2 are doing different things. I think
your theory about it mattering what the assignment source is is a red
herring.

I get different results than you do when running the test in CVS tip,
because of this post-8.0.1 bug fix:

2005-02-01 14:35 tgl

* src/pl/plpgsql/src/: pl_exec.c (REL8_0_STABLE), pl_exec.c: Adjust
plpgsql to allow assignment to an element of an array that is
initially NULL. For 8.0 we changed the main executor to have this
behavior in an UPDATE of an array column, but plpgsql's equivalent
case was overlooked. Per report from Sven Willenberger.

What I get is

regression=# select bad();
NOTICE: q=SELECT 1 AS x
NOTICE: myarray1[1][1]=1 myarray[1][1]=1
NOTICE: q=SELECT 2 AS x
ERROR: invalid array subscripts
CONTEXT: PL/pgSQL function "bad" line 14 at assignment
regression=#

and the reason is that the first loop iteration sets up the
previously-null arrays to have subscripts [1:1][1:1]. On the
second iteration you try to assign to [2][2] which is not adjacent
to the existing array bounds. Someday when we support null array
elements it might be sensible to allow this and fill the undefined
array entries with nulls ... but right now we can't do that.

regards, tom lane