Multidimensional arrays in plpgsql

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

Responses

Browse pgsql-novice by date

  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