加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

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

发布时间：2020-12-13 17:12:26 所属栏目：百科 来源：网络整理
导读：可以批量对数组元素进行删除，原文链接 http://blog.163.com/digoal@126/blog/static/163877040201261273149437/，在这篇 blog 中 德哥新增了函数 multi_text_array_remove (i_src text[],i_remove text[]) 用来应对数组中 多个元素删除的情况: 例如 数组 AR

可以批量对数组元素进行删除，原文链接
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','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,3],4);
array_append
--------------
{1,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,array[4]);
multi_array_append_int4
-------------------------
{1,4}
(1 row)

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

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

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


--1.3 multi_array_append_int4 函数测试 2

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

francs=> select multi_array_append_int4(array[1,array[:a,:b]);
multi_array_append_int4
-------------------------
{1,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,230)"> --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',array['d']);
multi_array_append_text
-------------------------
{a,c,d}
(1 row)

francs=> select multi_array_append_text(array['a',array['d','e']);
multi_array_append_text
-------------------------
{a,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,5]
要去掉一个或几个元素,3
变成ARRAY[1,5]
在几小时以前，要实现这个原子的操作,所有并发的进程必须使用for update的方式取数据,可以用以下方法。 

   
   

    
    begin;
   
   

   
   

    
    select column_array from table where pk_id=?for update; --这一步是防止其他进程对这条记录进行读取和修改。(这也是用BEGINCOMMIT;的原因)
   
   

   
   

    
    程序对column_array字段进行修改后,修改pk_id记录的值.锁时间长短和程序处理时间和网络交互时间有关.
   
   

   
   

    
    update table set column_array=ARRAY[1,45] =?
   
   

   
   

    
    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_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_upper
   
   

   
   

    
    arraycontained
   
   

   
   

    
    arraycontains
   
   

   
   

    
    arrayoverlap
   
   

   
   

    
    btarraycmp
   
   

   
   

    
    ginarrayconsistent
   
   

   
   

    
    ginarrayextract
   
   

   
   

    
    ginqueryarrayextract
   
   

   
   

    
    hash_array
   
   

   
   

    
    regexp_split_to_array
   
   

   
   

    
    string_to_array
   
   

   
   

    
    (47 rows)

将这些函数导入刚安装的PostgreSQL 9.3 devel中 : 
# 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_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_upper
>> arraycontained
>> arraycontains
>> arrayoverlap
>> btarraycmp
>> ginarrayconsistent
>> ginarrayextract
>> ginqueryarrayextract
>> hash_array
>> regexp_split_to_array
>> string_to_array
>> .


查看9.3比9.1.3多了哪些array相关的函数 : 
# 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
(7 rows)


今天要测试的是array_remove和array_replace这两个函数.
这两个函数的详细描述如下 : 
# 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)
# 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)


测试表 : 
# 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元素 : 
# update user_contact_info set contacts = array_replace(contacts,'13988888888','123456') where username='digoal';
UPDATE 1
id | username | phonenum | contacts
----+----------+-------------+----------------------------------------------
1 | digoal | 18657125281 | {123456,0)">(1 row)


测试remove元素 : 
# update user_contact_info set contacts = array_remove(contacts,0)">id | username | phonenum  |        contacts
----+----------+-------------+---------------------------------------
1 | digoal | 18657125281 | {13588888888,0)">(1 row)


【小结】
1. 以上利用array_remove,array_contact 进行的操作属于原子操作，不需要担心并发操作的问题。
2. 目前不能通过一次array_remove移除多个不等的元素,只能一次移除多个相等的元素,如果要一次移除多个不等的元素，需要在外面再包一层.例如 : 
# select array[1,1,4],array_remove(array[1,1),array_remove(array_remove(array[1,2);
array | array_remove | array_remove
-------------+--------------+--------------
{1,4} | {2,4} | {3,4}
(1 row)

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

  
  

   
   # 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','d']);
  
  

  
  

   
   multi_text_array_remove
  
  

  
  

   
   -------------------------
  
  

  
  

   
   {abc}
  
  

  
  

   
   (1 row)

3. 其他版本通过补丁的方式可以实现这个功能.
https://commitfest.postgresql.org/action/patch_view?id=872
怎么打补丁可参考 :
http://blog.163.com/digoal@126/blog/static/163877040201252885331153/
http://blog.163.com/digoal@126/blog/static/163877040201252884053930/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

相关内容
推荐文章
站长推荐
热点阅读

【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同