Thread: Multidimensional arrays in plpgsql
I don't know how to assign directly from a record into an element of a
multidimensional array. I seem to have to assign to a scalar first. Here's a
small program which shows what I can't and can do:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION bad() RETURNS NUMERIC AS $$
DECLARE
myarray1 INT[2][2]:=array[[NULL,NULL],[NULL,NULL]];
myarray2 INT[2][2]:=array[[NULL,NULL],[NULL,NULL]];
junk1 INT;
junk2 INT;
rec RECORD;
q TEXT;
BEGIN
FOR i IN 1..2 LOOP
FOR j IN 1..2 LOOP
q:='SELECT ' || i*j || ' AS x';
RAISE NOTICE 'q=%',q;
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;
END LOOP;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
----------------------------------------------------------------------
When I run the above, I get the following output:
NOTICE: q=SELECT 1 AS x
NOTICE: myarray1[1][1]=<NULL> myarray[1][1]=1
NOTICE: q=SELECT 2 AS x
NOTICE: myarray1[1][2]=<NULL> myarray[1][2]=2
NOTICE: q=SELECT 2 AS x
NOTICE: myarray1[2][1]=<NULL> myarray[2][1]=2
NOTICE: q=SELECT 4 AS x
NOTICE: myarray1[2][2]=<NULL> myarray[2][2]=4
bad
-----
0
(1 row)
How would I assign directly into a multidimensional array without using a
scalar? (I tested one dimensional arrays and I coul assign drectly into them).
Celia McInnis
"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
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)
"Celia McInnis" <celia@drmath.ca> writes:
> Tom, are you recommending that I don't use multidimensional arrays until some
> later version of postgres?
No, just that you not try to put nulls in them ;-)
Something like
myarray1 INT[2][2]:=array[[0,0],[0,0]];
woulda worked fine.
regards, tom lane
Hmmmm - but a little uglier in my real application where the arrays are of type TIMESTAMP. Is there anything other than a valid timestamp that I can use for a timestamp initializer - for instance some value that translates to epoch time, etc.? Celia McInnis > > Tom, are you recommending that I don't use multidimensional arrays until some > > later version of postgres? > > No, just that you not try to put nulls in them ;-) > > Something like > myarray1 INT[2][2]:=array[[0,0],[0,0]]; > woulda worked fine.
"Celia McInnis" <celia@drmath.ca> writes:
> Is there anything other than a valid timestamp that I can use for a timestamp
> initializer - for instance some value that translates to epoch time, etc.?
there are several special values, including 'epoch' ... see the docs.
regards, tom lane