可以批量对数组元素进行删除，原文链接
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); |
根据德哥的函数，依葫芦画瓢，这里写一个 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]); francs=> select multi_array_append_int4(array[1,2,3],null); |
备注：向数组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]); |
上面函数只是针对 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','e']); |
--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就已经支持原子操作了。
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;
1. 下载源码https://github.com/postgres/postgres/tarball/master2. 编译安装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 worldsudo gmake install-world3. 初始化数据库initdb -A md5 -D $PGDATA -E UTF8 --locale=C -W -U postgres4. 修改配置pg_hba.conf, postgresql.conf略5. 启动数据库pg_ctl start
postgres=# select proname from pg_proc where proname ~ 'array' order by proname;proname-----------------------_pg_expandarrayanyarray_inanyarray_outanyarray_recvanyarray_sendanynonarray_inanynonarray_outarray_aggarray_agg_finalfnarray_agg_transfnarray_appendarray_catarray_dimsarray_eqarray_fillarray_fillarray_gearray_gtarray_inarray_largerarray_learray_lengtharray_lowerarray_ltarray_ndimsarray_nearray_outarray_prependarray_recvarray_sendarray_smallerarray_to_stringarray_to_stringarray_upperarraycontainedarraycontainsarrayoverlapbtarraycmpginarrayconsistentginarrayextractginarrayextractginqueryarrayextracthash_arrayregexp_split_to_arrayregexp_split_to_arraystring_to_arraystring_to_array(47 rows)
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>> \.
postgres=# select proname from pg_proc where proname ~ 'array' and proname not in (select trim(proname) from pg91_array_funcs);proname------------------array_removearray_replacearray_typanalyzearraycontselarraycontjoinselarray_to_jsonarray_to_json(7 rows)
postgres=# \df+ *.*array_replace*List of functionsSchema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description------------+---------------+------------------+----------------------------------+--------+------------+----------+----------+---------------+---------------------------------------------------pg_catalog | array_replace | anyarray | anyarray, anyelement, anyelement | normal | immutable | postgres | internal | array_replace | replace any occurrences of an element in an array(1 row)postgres=# \df+ *.*array_remove*List of functionsSchema | 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 | remove 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 TABLEpostgres=# insert into user_contact_info (username,phonenum,contacts) values ('digoal','18657125281',ARRAY['13988888888','13588888888','18699999999','13881818181']);INSERT 0 1postgres=# select * from user_contact_info;id | username | phonenum | contacts----+----------+-------------+---------------------------------------------------1 | digoal | 18657125281 | {13988888888,13588888888,18699999999,13881818181}(1 row)
postgres=# update user_contact_info set contacts = array_replace(contacts, '13988888888', '123456') where username='digoal';UPDATE 1postgres=# select * from user_contact_info;id | username | phonenum | contacts----+----------+-------------+----------------------------------------------1 | digoal | 18657125281 | {123456,13588888888,18699999999,13881818181}(1 row)
postgres=# update user_contact_info set contacts = array_remove(contacts, '123456') where username='digoal';UPDATE 1postgres=# select * from user_contact_info;id | username | phonenum | contacts----+----------+-------------+---------------------------------------1 | digoal | 18657125281 | {13588888888,18699999999,13881818181}(1 row)
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)
postgres=# create or replace function multi_text_array_remove(i_src text[],i_remove text[]) returns text[] as $$declarev_text text;v_result text[];beginv_result := i_src;foreach v_text in ARRAY i_removeloopselect array_remove(v_result,v_text) into v_result;end loop;return v_result;end;$$ language plpgsql;CREATE FUNCTIONpostgres=# select multi_text_array_remove(ARRAY['abc','a','c','d'], ARRAY['a','c','d']);multi_text_array_remove-------------------------{abc}(1 row)
1708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
