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

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了PostgreSQL: array 数组类型添加元素 数组的使用，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

可以批量对数组元素进行删除，原文链接
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:@H_758_404@//github.com/postgres/postgres/tarball/master
   
   

   
   

    
    @H_758_404@2. 编译安装
   
   

   
   

    
    @H_758_404@useradd pg
   
   

   
   

    
    @H_758_404@./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
   
   

   
   

    
    @H_758_404@sudo gmake install-world
   
   

   
   

    
    @H_758_404@3. 初始化数据库
   
   

   
   

    
    @H_758_404@initdb -A md5 -D $PGDATA -E UTF8 --locale=C -W -U postgres
   
   

   
   

    
    @H_758_404@4. 修改配置pg_hba.conf,postgresql.conf
   
   

   
   

    
    @H_758_404@略
   
   

   
   

    
    @H_758_404@5. 启动数据库
   
   

   
   

    
    @H_758_404@pg_ctl start
6. 测试
在一台Postgresql 9.1.3的数据库中输出array相关的函数 : 

   
   

    
    postgres=@H_758_404@# select proname from pg_proc where proname ~ 'array' order by proname;
   
   

   
   

    
    @H_758_404@    proname    
   
   

   
   

    
    @H_758_404@-----------------------
   
   

   
   

    
    @H_758_404@_pg_expandarray
   
   

   
   

    
    @H_758_404@anyarray_in
   
   

   
   

    
    @H_758_404@anyarray_out
   
   

   
   

    
    @H_758_404@anyarray_recv
   
   

   
   

    
    @H_758_404@anyarray_send
   
   

   
   

    
    @H_758_404@anynonarray_in
   
   

   
   

    
    @H_758_404@anynonarray_out
   
   

   
   

    
    @H_758_404@array_agg
   
   

   
   

    
    @H_758_404@array_agg_finalfn
   
   

   
   

    
    @H_758_404@array_agg_transfn
   
   

   
   

    
    @H_758_404@array_append
   
   

   
   

    
    @H_758_404@array_cat
   
   

   
   

    
    @H_758_404@array_dims
   
   

   
   

    
    @H_758_404@array_eq
   
   

   
   

    
    @H_758_404@array_fill
   
   

   
   

    
    @H_758_404@array_ge
   
   

   
   

    
    @H_758_404@array_gt
   
   

   
   

    
    @H_758_404@array_in
   
   

   
   

    
    @H_758_404@array_larger
   
   

   
   

    
    @H_758_404@array_le
   
   

   
   

    
    @H_758_404@array_length
   
   

   
   

    
    @H_758_404@array_lower
   
   

   
   

    
    @H_758_404@array_lt
   
   

   
   

    
    @H_758_404@array_ndims
   
   

   
   

    
    @H_758_404@array_ne
   
   

   
   

    
    @H_758_404@array_out
   
   

   
   

    
    @H_758_404@array_prepend
   
   

   
   

    
    @H_758_404@array_recv
   
   

   
   

    
    @H_758_404@array_send
   
   

   
   

    
    @H_758_404@array_smaller
   
   

   
   

    
    @H_758_404@array_to_string
   
   

   
   

    
    @H_758_404@array_upper
   
   

   
   

    
    @H_758_404@arraycontained
   
   

   
   

    
    @H_758_404@arraycontains
   
   

   
   

    
    @H_758_404@arrayoverlap
   
   

   
   

    
    @H_758_404@btarraycmp
   
   

   
   

    
    @H_758_404@ginarrayconsistent
   
   

   
   

    
    @H_758_404@ginarrayextract
   
   

   
   

    
    @H_758_404@ginqueryarrayextract
   
   

   
   

    
    @H_758_404@hash_array
   
   

   
   

    
    @H_758_404@regexp_split_to_array
   
   

   
   

    
    @H_758_404@string_to_array
   
   

   
   

    
    @H_758_404@(47 rows)

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


查看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);
@H_758_404@ proname
@H_758_404@------------------
@H_758_404@array_remove
@H_758_404@array_replace
@H_758_404@array_typanalyze
@H_758_404@arraycontsel
@H_758_404@arraycontjoinsel
@H_758_404@array_to_json
@H_758_404@(7 rows)


今天要测试的是array_remove和array_replace这两个函数.
这两个函数的详细描述如下 : 
# \df+ *.*array_replace*
@H_758_404@ List of functions
@H_758_404@ Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Sou
@H_758_404@rce code | Description
@H_758_404@------------+---------------+------------------+----------------------------------+--------+------------+----------+----------+-----
@H_758_404@----------+---------------------------------------------------
@H_758_404@pg_catalog | array_replace | anyarray | anyarray,anyelement,anyelement | normal | immutable | postgres | internal | arra
@H_758_404@y_replace | replace any occurrences of an element in an array
@H_758_404@(1 row)
# \df+ *.*array_remove*
@H_758_404@ List of functions
@H_758_404@ Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code |
@H_758_404@ Description
@H_758_404@------------+--------------+------------------+----------------------+--------+------------+----------+----------+--------------+---
@H_758_404@-------------------------------------------------
@H_758_404@pg_catalog | array_remove | anyarray | anyarray,anyelement | normal | immutable | postgres | internal | array_remove | re
@H_758_404@move any occurrences of an element from an array
@H_758_404@(1 row)


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


测试replace元素 : 
# update user_contact_info set contacts = array_replace(contacts,'13988888888','123456') where username='digoal';
@H_758_404@UPDATE 1
@H_758_404@id | username | phonenum | contacts
@H_758_404@----+----------+-------------+----------------------------------------------
@H_758_404@ 1 | digoal | 18657125281 | {123456,0)">(1 row)


测试remove元素 : 
# update user_contact_info set contacts = array_remove(contacts,0)">id | username | phonenum  |        contacts
@H_758_404@----+----------+-------------+---------------------------------------
@H_758_404@ 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);
@H_758_404@ array | array_remove | array_remove
@H_758_404@-------------+--------------+--------------
@H_758_404@{1,4} | {2,4} | {3,4}
@H_758_404@(1 row)

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

  
  

   
   # create or replace function multi_text_array_remove(i_src text[],i_remove text[]) returns text[] as $$
  
  

  
  

   
   @H_758_404@declare
  
  

  
  

   
   @H_758_404@v_text text;        
  
  

  
  

   
   @H_758_404@v_result text[];    
  
  

  
  

   
   @H_758_404@begin                
  
  

  
  

   
   @H_758_404@v_result := i_src;                
  
  

  
  

   
   @H_758_404@foreach v_text in ARRAY i_remove
  
  

  
  

   
   @H_758_404@loop      
  
  

  
  

   
   @H_758_404@ select array_remove(v_result,v_text) into v_result;
  
  

  
  

   
   @H_758_404@end loop;     
  
  

  
  

   
   @H_758_404@return v_result;
  
  

  
  

   
   @H_758_404@end;
  
  

  
  

   
   @H_758_404@$$ language plpgsql;
  
  

  
  

   
   @H_758_404@CREATE FUNCTION
  
  

  
  

   
   @H_758_404@postgres=# select multi_text_array_remove(ARRAY['abc','d']);
  
  

  
  

   
   @H_758_404@multi_text_array_remove
  
  

  
  

   
   @H_758_404@-------------------------
  
  

  
  

   
   @H_758_404@{abc}
  
  

  
  

   
   @H_758_404@(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/
原文链接：https://www.f2er.com/postgresql/194852.html

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问