access and modify multidimensional arrays in postgres

Question

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.

Vao Tsun · Accepted Answer · 2017-06-14 07:46:47Z

1

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

answered Jun 14, 2017 at 7:46

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sennah Over a year ago

Thank you. That helps a lot.

Vao Tsun Over a year ago

my pleasure - glad it helped

Pavel Stehule Over a year ago

for initialization part can be used function array_fill: array_fill(null::double precision, array[3,3]);

Vao Tsun Over a year ago

thank you for your notice. I totally agree, that it would be neater, but OP would not see [[],[]] construct. I thought it was easier to understand this way - the whole OP was about not understanding multidimensional array, so I tried to "show" them

Collectives™ on Stack Overflow

access and modify multidimensional arrays in postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related