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

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

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

  1. CREATE OR REPLACE FUNCTION f_test01(
  2. )
  3. returns int[]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. FOR i IN 1..10000 LOOP
  8. v_list = array_append(v_list, i);
  9. END LOOP;
  10. return v_list;
  11. end;
  12. $BODY$
  13. LANGUAGE plpgsql;

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

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

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

  1. CREATE OR REPLACE FUNCTION f_test02(
  2. )
  3. returns int[]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. v_list := '{{11,12,13}, {21,22,23}}'::int[][];
  8. return v_list;
  9. end;
  10. $BODY$
  11. LANGUAGE plpgsql;

执行的结果如下

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

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

  1. CREATE OR REPLACE FUNCTION f_test03(
  2. )
  3. returns int[][]
  4. AS $BODY$
  5. declare v_list int[];
  6. begin
  7. v_list := array[1,2,3];
  8. return v_list;
  9. end;
  10. $BODY$
  11. LANGUAGE plpgsql;

执行的结果如下:

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

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

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

  1. CREATE OR REPLACE FUNCTION f_test04(
  2. )
  3. returns int[][]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. v_list := '{{0}}'::int[][];
  8. v_list[1] = array[1,2,3];
  9. return v_list;
  10. end;
  11. $BODY$
  12. LANGUAGE plpgsql;

报错信息如下:

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

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

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

  1. CREATE OR REPLACE FUNCTION f_test05(
  2. )
  3. returns int[][]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. v_list := '{{0}}'::int[][];
  8. v_list[1] := array[1];
  9. return v_list;
  10. end;
  11. $BODY$
  12. LANGUAGE plpgsql;

执行

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

仍然出错。

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

  1. CREATE OR REPLACE FUNCTION f_test06(
  2. )
  3. returns int[][]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. v_list := '{{0}}'::int[][];
  8. v_list[1][1] = 10;
  9. return v_list;
  10. end;
  11. $BODY$
  12. LANGUAGE plpgsql;

执行不报错:

  1. osdba=# select f_test06();
  2. f_test06
  3. ----------
  4. {{10}}
  5. (1 row)

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

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

  1. CREATE OR REPLACE FUNCTION f_test07(
  2. )
  3. returns int[][]
  4. AS $BODY$
  5. declare v_list int[][];
  6. begin
  7. v_list := '{{0}}'::int[][];
  8. v_list = array_append(v_list, array[1,2,3]);
  9. return v_list;
  10. end;
  11. $BODY$
  12. LANGUAGE plpgsql;

报错信息如下:

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

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

  1. CREATE TYPE int_list AS (v int[]);
  2. CREATE OR REPLACE FUNCTION f_test08(
  3. )
  4. returns int_list[]
  5. AS $BODY$
  6. declare v_list int_list[];
  7. begin
  8. v_list := array['("{0,0,0}")'::int_list];
  9. v_list = array_append(v_list, '("{1,2,3}")'::int_list);
  10. return v_list;
  11. end;
  12. $BODY$
  13. LANGUAGE plpgsql;

执行结果如下:

  1. osdba=# select f_test08();
  2. f_test08
  3. -----------------------------------
  4. {"(\"{0,0,0}\")","(\"{1,2,3}\")"}
  5. (1 row)
2 评论  
RomyCai · 2L · 2018-10-19 19:22:03

帮助良多

RomyCai · 1L · 2018-10-19 19:21:52

帮助良多!

添加一条新评论