Skip site navigation (1) Skip section navigation (2)

Re: Multidimensional arrays in plpgsql

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Celia McInnis" <celia(at)drmath(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Multidimensional arrays in plpgsql
Date: 2005-03-17 23:33:43
Message-ID: 28543.1111102423@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

In response to

Responses

pgsql-novice by date

Next:From: Morgan KitaDate: 2005-03-18 02:18:33
Subject: Question on simulating Enum Data type
Previous:From: Celia McInnisDate: 2005-03-17 23:05:50
Subject: Multidimensional arrays in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group