今天在德哥 blog 中学习到一个不错的数组函数，可以批量对数组元素进行删除，原文链接 http://blog.163.com/digoal@126/blog/static/163877040201261273149437/，在这篇 blog 中 德哥新增了函数 multi_text_array_remove (i_src text[],i_remove text[]) 用来应对数组中多个元素删除的情况，例如数组 ARRAY[1,2,3,4,5] 如果要去掉一个元素，可以用 array_remove 函数 ( 这个函数在9.3 版本中才会有 )，但这个函数只能删除一个元素，如果要去除多个元素，则可调用函数 multi_text_array_remove

Multi_text_array_remove 函数演示

1
2
3
4
5

postgres=# select multi_text_array_remove(ARRAY['abc','a','c','d'], ARRAY['a','c','d']);  
multi_text_array_remove  
-------------------------  
 {abc}  
(1 row)

那么添加数组元素情况如何呢？在 PostgreSQL 中已经有函数 array_append 函数，但是这个函数只能一次添加一个元素，如果想添加多个，需要多次调用。

Array_append 函数演示

1
2
3
4
5
6
7
8
9
10
11
12

francs=> /df array_append  
 List of functions  
 Schema | Name | Result data type | Argument data types | Type  
------------+--------------+------------------+----------------------+--------  
pg_catalog | array_append | anyarray | anyarray, anyelement | normal  
(1 row)

francs=> select array_append(array[1,2,3],4);  
array_append  
--------------  
{1,2,3,4}  
(1 row)

根据德哥的函数，依葫芦画瓢，这里写一个 int4[] 类型数组元素批量增加的函数

创建 multi_array_append_int4 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

create or replace function multi_array_append_int4(i_src int4[],i_append int4[]) returns text[] AS $$  
DECLARE  
 v_text int4;  
 v_result int4[];  
 BEGIN  
   
  v_result := i_src;  
   
 if i_append is null then  
   return v_result;  
 end if;  
  
 foreach v_text in ARRAY i_append loop  
 select array_append(v_result,v_text) into v_result;  
 end loop;  
  
 return v_result;  
  
 END;  
$$ LANGUAGE 'plpgsql';

备注： 其中 “foreach v_text in ARRAY i_append loop “ 代码是用来遍历数组中的每个元素，具体语法可参考本文末尾的附一。

multi_array_append_int4 函数测试 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

francs=> select multi_array_append_int4(array[1,2,3],array[4]);  
multi_array_append_int4  
-------------------------  
{1,2,3,4}  
(1 row)

francs=> select multi_array_append_int4(array[1,2,3],array[4,5]);  
multi_array_append_int4  
-------------------------  
{1,2,3,4,5}  
(1 row)

francs=> select multi_array_append_int4(array[1,2,3],null);  
multi_array_append_int4  
-------------------------  
{1,2,3}  
(1 row)

备注：向数组array[1,2,3] 末尾追加元素。

multi_array_append_int4 函数测试 2

1
2
3
4
5
6
7

francs=> set a 4  
francs=> set b 5

francs=> select multi_array_append_int4(array[1,2,3],array[:a,:b]);  
multi_array_append_int4  
-------------------------  
{1,2,3,4,5}

上面函数只是针对 integer 类型的，如果是字符类型就不行，同理可以写个函数。

创建 multi_array_append_text 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

create or replace function multi_array_append_text(i_src text[],i_append text[]) returns text[] AS $$  
 DECLARE  
 v_text text;  
 v_result text[];  
 BEGIN  
   
 v_result := i_src;  
   
 if i_append is null then  
   return v_result;  
 end if;  
  
 foreach v_text in ARRAY i_append loop  
 select array_append(v_result,v_text) into v_result;  
 end loop;  
  
 return v_result;  
  
 END;  
$$ LANGUAGE 'plpgsql';

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

francs=> select multi_array_append_text(array['a','b','c'],null);  
multi_array_append_text  
-------------------------  
{a,b,c}  
(1 row)
  
francs=> select multi_array_append_text(array['a','b','c'],array['d']);  
multi_array_append_text  
-------------------------  
{a,b,c,d}  
(1 row)

francs=> select multi_array_append_text(array['a','b','c'],array['d','e']);  
multi_array_append_text  
-------------------------  
{a,b,c,d,e}  
(1 row)

附一 : Looping Through Arrays

The FOREACH loop is much like a FOR loop, but instead of iterating through the rows returned by a SQL query, it iterates through the elements of an array value. (In general, FOREACH is meant for looping through components of a composite-valued expression; variants for looping through composites besides arrays may be added in future.) The FOREACH statement to loop over an array is:
[ <> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];

参考

• http://blog.163.com/digoal@126/blog/static/163877040201261273149437/
• http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html
• http://www.depesz.com/2012/07/12/waiting-for-9-3-add-array_remove-and-array_replace-functions/comment-page-1/#comment-35948