Re: Multidimensional arrays in plpgsql - Mailing list pgsql-novice

Tom, are you recommending that I don't use multidimensional arrays until some
later version of postgres? I don't actually have any need to null out array
elements, since I will be feeding data into them. I only did the nulling in
attempting to define the arrays. Is there some other way that I can define a
multidimensionl array and then be able to pack data into it? (I'm definitely a
newbie, so don't assume that I am sensible in how I was trying to define or
use these arrays :-)).

My code will be a lot simpler and more compact if I can use multidimensional
arrays (3 dimensional would be perfect for my current need), so if they work,
I'd definitely like to use them.

Thanks for any advice and suggestions which you can give,
Celia McInnis

On Thu, 17 Mar 2005 18:33:43 -0500, Tom Lane wrote
> "Celia McInnis" <celia@drmath.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


--
Open WebMail Project (http://openwebmail.org)