CODEWITHSUNDEEP
sqlarrayspostgresqlmultidimensional-arrayplpgsql
sennah
sennah

Reputation: 13

access and modify multidimensional arrays in postgres

I can't figure out how to change multidimensional arrays in postgres. Let's say there is the following code:

do
$$
declare 
    a double precision[][];
    x integer;
    y integer;

begin
    for x in 1..3 loop
        for y in 1..3 loop
            a[x y]:= x * y;
            raise notice 'x: %, y: %, value: %, should be: %',x, y,   a[x:y], x*y;
        end loop;
    end loop;
end 
$$
language plpgsql;

The result is the following:

x: 1, y: 1, value: {1}, should be: 1
x: 1, y: 2, value: {2}, should be: 2
x: 1, y: 3, value: {3}, should be: 3
x: 2, y: 1, value: {}, should be: 2
x: 2, y: 2, value: {4}, should be: 4
x: 2, y: 3, value: {6}, should be: 6
x: 3, y: 1, value: {}, should be: 3
x: 3, y: 2, value: {}, should be: 6
x: 3, y: 3, value: {9}, should be: 9

As you can see, there are some problems. For example the combination of x=2 and y=1 results in {}.

Normally I would think that I can change an array by

a[x][y]:= value;

but that produces an error.

Upvotes: 1

Views: 1404

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

Your example operates one-dimension array - you can check it with array_ndims(a). or just raise info '%',a;.

Instead try a[x][y]:= value; approach with explicitly defining dimensions to avoid error, eg:

do
$$
declare
    a double precision[][];
    x integer;
    y integer;

begin
    a := array[[NULL,NULL,NULL],[NULL,NULL,NULL],[NULL,NULL,NULL]];
    for x in 1..3 loop
        for y in 1..3 loop
            a[x][y]:= x * y;
            raise notice 'x: %, y: %, value: %, should be: %',x, y,   a[x][y], x*y;
        end loop;
    end loop;
    raise info '%',a;
end
$$
language plpgsql;
NOTICE:  x: 1, y: 1, value: 1, should be: 1
NOTICE:  x: 1, y: 2, value: 2, should be: 2
NOTICE:  x: 1, y: 3, value: 3, should be: 3
NOTICE:  x: 2, y: 1, value: 2, should be: 2
NOTICE:  x: 2, y: 2, value: 4, should be: 4
NOTICE:  x: 2, y: 3, value: 6, should be: 6
NOTICE:  x: 3, y: 1, value: 3, should be: 3
NOTICE:  x: 3, y: 2, value: 6, should be: 6
NOTICE:  x: 3, y: 3, value: 9, should be: 9
INFO:  {{1,2,3},{2,4,6},{3,6,9}}
DO

Also mind - I changed colon slicing to exact index in raise

Upvotes: 1

Related Questions