How can we define a 3-dimensional array of geometry?

Question

I want to write a code for postgis in pgAdmin that is based on postgresql. It defines a 3-dimensional array of geometry.

I used this code for it:

DECLARE
    G geometry[9][9][9];
BEGIN
    for i IN 1..9 LOOP
    for j IN 1..9 LOOP
    for k IN 1..9 LOOP
        G[i][j][k] := [value that I want];
    END LOOP;
    END LOOP;
    END LOOP;

But it returned this error:

"array subscript out of range"

I used this instead:

DECLARE
    G geometry[9][9][9];
BEGIN
    for i IN array_lower(G, 1)..array_upper(G, 1) LOOP
    for j IN array_lower(G, 1)..array_upper(G, 1) LOOP
    for k IN array_lower(G, 1)..array_upper(G, 1) LOOP
        G[i][j][k] := [value that I want];
    END LOOP;
    END LOOP;
    END LOOP;

I have a different error this time:

"lower bound of FOR loop cannot be null"

I used this in BEGIN Part and solved all the errors:

G[1][1][1] := '01010000200400000000000000000000000000000000000000';

But I think this is not true and doesn't calculate all of the iterations for loops. I think this takes in account only the G[1][1][1]. What should I do now?

score 2 · Accepted Answer · answered Feb 04 '14 at 12:55

In PostgreSQL, simply declaring the array dimensions does not initialize or pre-allocate anything. The array is dynamic in all its dimensions. That differs significantly from the multi-dimensional array implementations found in general programming language like C.

To mimic the logic of these languages, you may first initialize the 9x9x9 array with a statement like this:

G:=array_fill('point(0 0)'::geometry, array[9,9,9]);

Then the rest of the code will just work when refering to G[i][j][k] either as source or destination of assignments.

Yes. Assignments to big arrays are comparatively slow, though. [Craig provides details here.](http://stackoverflow.com/questions/16992339/why-is-postgresql-array-access-so-much-faster-in-c-than-in-pl-pgsql) — Erwin Brandstetter, Feb 04 '14 at 20:16

How can we define a 3-dimensional array of geometry?

1 Answers1