## Multidimensional arrays in plpgsql

From: "Celia McInnis" pgsql-novice(at)postgresql(dot)org Multidimensional arrays in plpgsql 2005-03-17 23:05:50 20050317225431.M86882@drmath.ca (view raw, whole thread or download thread mbox) 2005-03-17 23:05:50 from "Celia McInnis"  2005-03-17 23:33:43 from Tom Lane   2005-03-18 18:23:42 from "Celia McInnis"    2005-03-18 18:26:03 from Tom Lane     2005-03-18 19:32:31 from "Celia McInnis"      2005-03-18 19:44:28 from Tom Lane 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

```

### pgsql-novice by date

 Next: From: Tom Lane Date: 2005-03-17 23:33:43 Subject: Re: Multidimensional arrays in plpgsql Previous: From: George Weaver Date: 2005-03-17 20:02:38 Subject: Re: XML and Postgres

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