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