PL/pgSQL中使用二维数组需要注意的问题

在PL/pgSQL中使用数组类型有一些注意点，如果不注意，会出一些稀奇古怪的问题。

1. 数组在初始化之后元素个数是不能改变的

首先需要注意的是在PL/pgSQL中数组在初始化之后元素个数是不能改变的，使用array_append函数添加元素时实际上是生成了一个新的数组，如下面的例子：

CREATE OR REPLACE FUNCTION f_test01(
)
returns int[]
AS $BODY$
  declare v_list int[][];
begin
    FOR i IN 1..10000 LOOP 
       v_list = array_append(v_list, i);
    END LOOP;
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

在上面的循环中，通过v_list=array_append(v_list,i)
添加元素，实际上效率不高的，原因是每次迭代，都需要把原先v_list中的全部内容再拷贝一次，越循环到最后，随着v_list中的元素的增加，每次迭代中，拷贝的数据就越多。如循环到第9999时，v_list中已有了9999个元素，最后一次迭代，需要把v_list已有的9999个元素和最后添加元素(值为10000)的拷贝到新数组中。

2. 可以是多维数组，但维数是在初始化时决定的，而不是定义的时候

如下面的例子，函数的返回值定义的是一维数组，但实际上可以返回二维数组：

CREATE OR REPLACE FUNCTION f_test02(
)
returns int[]
AS $BODY$
  declare v_list int[][];
begin
    v_list := '{{11,12,13}, {21,22,23}}'::int[][];
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

执行的结果如下

osdba=# select f_test02();
        f_test02
-------------------------
 {{11,12,13},{21,22,23}}
(1 row)

下面的例子中函数的返回值定义的是二维数组，但实际上可以返回一维数组：

CREATE OR REPLACE FUNCTION f_test03(
)
returns int[][]
AS $BODY$
  declare v_list int[];
begin
    v_list := array[1,2,3];
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

执行的结果如下：

osdba=# select f_test03();
 f_test03
----------
 {1,2,3}
(1 row)

3. 二维数组会出现一些奇怪的问题

下面的函数在执行的时候会报错：

CREATE OR REPLACE FUNCTION f_test04(
)
returns int[][]
AS $BODY$
  declare v_list int[][];
begin
    v_list := '{{0}}'::int[][];
    v_list[1] = array[1,2,3];
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

报错信息如下：

osdba=# select f_test04();
ERROR: invalid input syntax for integer: "{1,2,3}"
CONTEXT: PL/pgSQL function f_test04() line 5 at assignment

其实数组一旦初始化，其占用的内存空间就固定了，所以无法改变。因为是二维数组，因为其元素也是一个数组，所以二维数组的元素的空间也被固定了，也不能改变，所以不能被赋值。

上面的例子中初始化语句v_list := '{{0}}'::int[][];
中，初始化了一个二维数据，这个二维数据，只有一个元素，而这一个元素中也只有一项值，有人会问是不是因为赋值时，赋了三个值的数组array[1,2,3]
，如果只赋只有一个值的数组有可能不报错了，如array[1]
，我们试一下：

CREATE OR REPLACE FUNCTION f_test05(
)
returns int[][]
AS $BODY$
  declare v_list int[][];
begin
    v_list := '{{0}}'::int[][];
    v_list[1] := array[1];
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

执行

osdba=# select f_test05();
ERROR: invalid input syntax for integer: "{1}"
CONTEXT: PL/pgSQL function f_test05() line 5 at assignment

仍然出错。

直接对二维数组的最底层元素赋值是可以的，如下所示：

CREATE OR REPLACE FUNCTION f_test06(
)
returns int[][]
AS $BODY$
  declare v_list int[][];
begin
    v_list := '{{0}}'::int[][];
    v_list[1][1] = 10;
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

执行不报错：

osdba=# select f_test06();
 f_test06
----------
 {{10}}
(1 row)

4. 在一些无法判断出维度的表达式中会出错

虽然可以是多维数组，但由于维度不是在定义时指定的，在一些表达式中会出错，如在用array_append给二维数组中添加一个一维数组的元素时会报错：

CREATE OR REPLACE FUNCTION f_test07(
)
returns int[][]
AS $BODY$
  declare v_list int[][];
begin
    v_list := '{{0}}'::int[][];
    v_list = array_append(v_list, array[1,2,3]);
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

报错信息如下：

osdba=# select f_test07();
ERROR: function array_append(integer[], integer[]) does not exist
LINE 1: SELECT array_append(v_list, array[1,2,3])
               ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT array_append(v_list, array[1,2,3])
CONTEXT: PL/pgSQL function f_test07() line 5 at assignment

这时我们可以先定义一个一维数据的类型，然后再返回这个类型的数组，相当于实现的二维数组，如下面的例子：

CREATE TYPE int_list AS (v int[]);
CREATE OR REPLACE FUNCTION f_test08(
)
returns int_list[]
AS $BODY$
  declare v_list int_list[];
begin
    v_list := array['("{0,0,0}")'::int_list];
    v_list = array_append(v_list, '("{1,2,3}")'::int_list);
    return v_list;
end;
$BODY$
LANGUAGE plpgsql;

执行结果如下：

osdba=# select f_test08();
            f_test08
-----------------------------------
 {"(\"{0,0,0}\")","(\"{1,2,3}\")"}
(1 row)