Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

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