From: | "Celia McInnis" <celia(at)drmath(dot)ca> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Multidimensional arrays in plpgsql |
Date: | 2005-03-17 23:05:50 |
Message-ID: | 20050317225431.M86882@drmath.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-17 23:33:43 | Re: Multidimensional arrays in plpgsql |
Previous Message | George Weaver | 2005-03-17 20:02:38 | Re: XML and Postgres |