CODEWITHSUNDEEP
arrayspostgresqlloops
Baker
Baker

Reputation: 505

PostgreSQL: ERROR: array subscript out of range

I am trying to fill two dimensional array

do $$

declare pole text[][];

begin
for y in 1..6
loop
  for x in 1..4
  loop
    pole[y][x] = '0';
    raise notice 'x: %',x;
    raise notice 'y: %',y;
  end loop;
end loop;

/*
pole  := '{
{0,0,0,0}, 
{7,0,0,0},
{0,0,0,0},
{0,0,0,0},
{0,0,0,0}
}';
*/

raise notice 'pole : %', pole;
raise notice 'pole one: %', pole[2][1];

end $$

but getting ERROR: array subscript out of range, If I fill the array manually like pole[6][4] := '0' it has no problem, but once the loop is used I get error, I don't know why, variables are between 1 and 4 and 1 and 6 and manual assignment works. This is basic programming am I missing something?

1) SQL State: 00000 --- x: 1
2) SQL State: 00000 --- y: 1
3) SQL State: 00000 --- x: 2
4) SQL State: 00000 --- y: 1
5) SQL State: 00000 --- x: 3
6) SQL State: 00000 --- y: 1
7) SQL State: 00000 --- x: 4
8) SQL State: 00000 --- y: 1
9) SQL State: 00000 --- x: 1
10) SQL State: 00000 --- y: 2
11) SQL State: 00000 --- x: 2
12) SQL State: 00000 --- y: 2
13) SQL State: 00000 --- x: 3
14) SQL State: 00000 --- y: 2
15) SQL State: 00000 --- x: 4
16) SQL State: 00000 --- y: 2
17) SQL State: 00000 --- x: 1
18) SQL State: 00000 --- y: 3
19) SQL State: 00000 --- x: 2
20) SQL State: 00000 --- y: 3
21) SQL State: 00000 --- x: 3
22) SQL State: 00000 --- y: 3
23) SQL State: 00000 --- x: 4
24) SQL State: 00000 --- y: 3
25) SQL State: 00000 --- x: 1
26) SQL State: 00000 --- y: 4
27) SQL State: 00000 --- x: 2
28) SQL State: 00000 --- y: 4
29) SQL State: 00000 --- x: 3
30) SQL State: 00000 --- y: 4
31) SQL State: 00000 --- x: 4
32) SQL State: 00000 --- y: 4
33) SQL State: 00000 --- x: 1
34) SQL State: 00000 --- y: 5
35) SQL State: 00000 --- x: 2
36) SQL State: 00000 --- y: 5
37) SQL State: 00000 --- x: 3
38) SQL State: 00000 --- y: 5
39) SQL State: 00000 --- x: 4
40) SQL State: 00000 --- y: 5
41) SQL State: 00000 --- x: 1
42) SQL State: 00000 --- y: 6
43) SQL State: 00000 --- x: 2
44) SQL State: 00000 --- y: 6
45) SQL State: 00000 --- x: 3
46) SQL State: 00000 --- y: 6
47) SQL State: 00000 --- x: 4
48) SQL State: 00000 --- y: 6

PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Upvotes: 4

Views: 4197

Answers (1)

JGH
JGH

Reputation: 17906

Multidimensional array cannot grow like 1D arrays

A stored array value can be enlarged by assigning to elements not already present. Any positions between those previously present and the newly assigned elements will be filled with nulls. For example, if array myarray currently has 4 elements, it will have six elements after an update that assigns to myarray[6]; myarray[5] will contain null. Currently, enlargement in this fashion is only allowed for one-dimensional arrays, not multidimensional arrays.

So you would have to initialize the array first, then populate it.

do $$

declare pole text[][];

begin

pole := array_fill(null::text, array[6,4]);

for y in 1..6
loop
  for x in 1..4
  loop

    raise notice 'x: %',x;
    raise notice 'y: %',y;
    pole[y][x] = '0';
  end loop;
end loop;


raise notice 'pole : %', pole;
raise notice 'pole one: %', pole[2][1];

end $$;

Upvotes: 4

Related Questions