关闭

PostgreSQL: array 数组类型添加元素 数组的使用

标签: postgresql数组函数
2927人阅读 评论(0) 收藏 举报
分类:

可以批量对数组元素进行删除,原文链接
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 函数演示

 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 函数演示

 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[] 类型数组元素批量增加的函数


--1.1  创建  multi_array_append_int4 函数

 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 " 代码是用来遍历数组中的每个元素,具体语法
               可参考本文末尾的附一。


--1.2  multi_array_append_int4 函数测试 1

 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] 末尾追加元素。


--1.3  multi_array_append_int4 函数测试 2

 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 类型的,如果是字符类型就不行,同理可以写个函数。
 
 
 --2.1  创建 multi_array_append_text 函数 

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

--2.2  测试

 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)

   


--3  附一 : 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:

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];


--4  参考
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


前段时间一位开发的同事问我,ARRAY类型有没有原子的替换和删除ARRAY元素的操作,用于好友列表(array类型)的更新,删除好友用得比较多。替换操作可能用得比较少。添加好友的话现在的PostgreSQL就已经支持原子操作了。 

例如 : 
ARRAY[1,2,3,4,5]
要去掉一个或几个元素, 2,3
变成ARRAY[1,4,5]
在几小时以前,要实现这个原子的操作, 所有并发的进程必须使用for update的方式取数据, 可以用以下方法。 

begin;
select column_array from table where pk_id=? for update; -- 这一步是防止其他进程对这条记录进行读取和修改。(这也是用BEGIN;COMMIT;的原因)
-- 程序对column_array字段进行修改后, 修改pk_id记录的值.锁时间长短和程序处理时间和网络交互时间有关.
update table set column_array=ARRAY[1,4,5] where pk_id=?
commit;

了解PostgreSQL的朋友一定知道, BEGIN;COMMIT; 比autocommit的开销大一些, 能不用就尽量不用。(具体为什么可以去参考一下src/backend/access/transam)
对于这种操作如果能有ARRAY类型的原子操作,就不需要锁记录了,autocommit即可。
就在几个小时前,TOM LANE提交了这个功能 . 
允许对ARRAY类型进行元素的替换和移除操作,在此之前,ARRAY类型加元素是有函数和操作符的原子操作。但是没有替换元素和删除元素的原子操作。
添加这两个函数后,ARRAY的功能又更加强大了。
下面来试一下这个新功能 : 
安装详细步骤略,有兴趣的朋友参考我以前的BLOG,有很多关于安装和配置的过程。
简要安装步骤 : 

1. 下载源码
https://github.com/postgres/postgres/tarball/master
2. 编译安装
useradd pg
./configure --prefix=/home/pg/pgsql --with-pgport=5433 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-debug --with-wal-blocksize=16 && gmake world
sudo gmake install-world
3. 初始化数据库
initdb -A md5 -D $PGDATA -E UTF8 --locale=C -W -U postgres
4. 修改配置pg_hba.conf, postgresql.conf
5. 启动数据库
pg_ctl start

6. 测试
在一台PostgreSQL 9.1.3的数据库中输出array相关的函数 : 

postgres=# select proname from pg_proc where proname ~ 'array' order by proname;
        proname        
-----------------------
 _pg_expandarray
 anyarray_in
 anyarray_out
 anyarray_recv
 anyarray_send
 anynonarray_in
 anynonarray_out
 array_agg
 array_agg_finalfn
 array_agg_transfn
 array_append
 array_cat
 array_dims
 array_eq
 array_fill
 array_fill
 array_ge
 array_gt
 array_in
 array_larger
 array_le
 array_length
 array_lower
 array_lt
 array_ndims
 array_ne
 array_out
 array_prepend
 array_recv
 array_send
 array_smaller
 array_to_string
 array_to_string
 array_upper
 arraycontained
 arraycontains
 arrayoverlap
 btarraycmp
 ginarrayconsistent
 ginarrayextract
 ginarrayextract
 ginqueryarrayextract
 hash_array
 regexp_split_to_array
 regexp_split_to_array
 string_to_array
 string_to_array
(47 rows)


将这些函数导入刚安装的PostgreSQL 9.3 devel中 : 

postgres=#  create table pg91_array_funcs(proname text);
postgres=# copy pg91_array_funcs from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>  _pg_expandarray
>>  anyarray_in
>>  anyarray_out
>>  anyarray_recv
>>  anyarray_send
>>  anynonarray_in
>>  anynonarray_out
>>  array_agg
>>  array_agg_finalfn
>>  array_agg_transfn
>>  array_append
>>  array_cat
>>  array_dims
>>  array_eq
>>  array_fill
>>  array_fill
>>  array_ge
>>  array_gt
>>  array_in
>>  array_larger
>>  array_le
>>  array_length
>>  array_lower
>>  array_lt
>>  array_ndims
>>  array_ne
>>  array_out
>>  array_prepend
>>  array_recv
>>  array_send
>>  array_smaller
>>  array_to_string
>>  array_to_string
>>  array_upper
>>  arraycontained
>>  arraycontains
>>  arrayoverlap
>>  btarraycmp
>>  ginarrayconsistent
>>  ginarrayextract
>>  ginarrayextract
>>  ginqueryarrayextract
>>  hash_array
>>  regexp_split_to_array
>>  regexp_split_to_array
>>  string_to_array
>>  string_to_array
>> \.


查看9.3比9.1.3多了哪些array相关的函数 : 

postgres=# select proname from pg_proc where proname ~ 'array' and proname not in (select trim(proname) from pg91_array_funcs);
     proname      
------------------
 array_remove
 array_replace
 array_typanalyze
 arraycontsel
 arraycontjoinsel
 array_to_json
 array_to_json
(7 rows)


今天要测试的是array_remove和array_replace这两个函数.
这两个函数的详细描述如下 : 

postgres=# \df+ *.*array_replace*
                                                                                        List of functions
   Schema   |     Name      | Result data type |       Argument data types        |  Type  | Volatility |  Owner   | Language |  Sou
rce code  |                    Description                    
------------+---------------+------------------+----------------------------------+--------+------------+----------+----------+-----
----------+---------------------------------------------------
 pg_catalog | array_replace | anyarray         | anyarray, anyelement, anyelement | normal | immutable  | postgres | internal | arra
y_replace | replace any occurrences of an element in an array
(1 row)
postgres=# \df+ *.*array_remove*
                                                                                  List of functions
   Schema   |     Name     | Result data type | Argument data types  |  Type  | Volatility |  Owner   | Language | Source code  |   
                 Description                     
------------+--------------+------------------+----------------------+--------+------------+----------+----------+--------------+---
-------------------------------------------------
 pg_catalog | array_remove | anyarray         | anyarray, anyelement | normal | immutable  | postgres | internal | array_remove | re
move any occurrences of an element from an array
(1 row)


测试表 : 

postgres=# create table user_contact_info(id serial primary key, username text unique, phonenum text, contacts text[]);
CREATE TABLE
postgres=# insert into user_contact_info (username,phonenum,contacts) values ('digoal','18657125281',ARRAY['13988888888','13588888888','18699999999','13881818181']);
INSERT 0 1
postgres=# select * from user_contact_info;
 id | username |  phonenum   |                     contacts                      
----+----------+-------------+---------------------------------------------------
  1 | digoal   | 18657125281 | {13988888888,13588888888,18699999999,13881818181}
(1 row)


测试replace元素 : 

postgres=# update user_contact_info set contacts = array_replace(contacts, '13988888888', '123456') where username='digoal';
UPDATE 1
postgres=# select * from user_contact_info;
 id | username |  phonenum   |                   contacts                   
----+----------+-------------+----------------------------------------------
  1 | digoal   | 18657125281 | {123456,13588888888,18699999999,13881818181}
(1 row)


测试remove元素 : 

postgres=# update user_contact_info set contacts = array_remove(contacts, '123456') where username='digoal';
UPDATE 1
postgres=# select * from user_contact_info;
 id | username |  phonenum   |               contacts                
----+----------+-------------+---------------------------------------
  1 | digoal   | 18657125281 | {13588888888,18699999999,13881818181}
(1 row)


【小结】
1. 以上利用array_remove, array_contact 进行的操作属于原子操作,不需要担心并发操作的问题。
2. 目前不能通过一次array_remove移除多个不等的元素, 只能一次移除多个相等的元素, 如果要一次移除多个不等的元素,需要在外面再包一层.例如 : 

postgres=# select array[1,1,2,3,4],array_remove(array[1,1,2,3,4],1),array_remove(array_remove(array[1,1,2,3,4],1),2);
    array    | array_remove | array_remove 
-------------+--------------+--------------
 {1,1,2,3,4} | {2,3,4}      | {3,4}
(1 row)

如果觉得嵌套比较麻烦, 可以写个对应的plpgsql函数来实现一次删除多个元素的场景, 例如 : 

postgres=# create or replace function multi_text_array_remove(i_src text[],i_remove text[]) returns text[] as $$
declare
v_text text;                 
v_result text[];        
begin                                
v_result := i_src;                                 
foreach v_text in ARRAY i_remove 
loop            
  select array_remove(v_result,v_text) into v_result;
end loop;           
return v_result;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select multi_text_array_remove(ARRAY['abc','a','c','d'], ARRAY['a','c','d']);
 multi_text_array_remove 
-------------------------
 {abc}
(1 row)


3. 其他版本通过补丁的方式可以实现这个功能.
怎么打补丁可参考 : 






0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:391862次
    • 积分:4700
    • 等级:
    • 排名:第6407名
    • 原创:17篇
    • 转载:382篇
    • 译文:0篇
    • 评论:31条
    最新评论