arrays - PostgreSQL:错误:数组下标超出范围

Question

我正在尝试填充二维数组

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 $$

但出现错误:数组下标超出范围，如果我像 pole[6][4] := '0' 那样手动填充数组，它没有问题，但是一旦使用循环我收到错误消息，我不知道为什么，变量介于 1 和 4 以及 1 和 6 之间并且手动分配有效。这是基本编程，我错过了什么吗？

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

Answer 1

多维数组cannot grow像一维数组

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.

所以你必须先初始化数组，然后填充它。

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 $$;