array_agg:
聚合函数,返回一个array,相当于oracle的wm_concat:
digoal=# select array_agg(fooid) from foo; array_agg ----------------- {4,5,7,6,1,7,8} (1 row)
setof转换为array:
digoal=# select array(select fooid from foo); array ----------------- {4,5,7,6,1,7,8} (1 row)
bit_and,bit_or:
bit_and:所有非空的输入值的按位与。
bit_or:所有非空的输入值的按位或。
postgres=# select bit_and(1|3);
bit_and
---------
3
(1 row)
postgres=# select bit_and(1&3);
bit_and
---------
1
(1 row)
postgres=# select * from ct;
id | rowid | attribute | value
----+-------+-----------+-------
1 | test1 | att1 | val1
2 | test1 | att2 | val2
3 | test1 | att3 | val3
4 | test1 | att4 | val4
5 | test2 | att1 | val5
6 | test2 | att2 | val6
7 | test2 | att3 | val7
8 | test2 | att4 | val8
(8 rows)
postgres=# select bit_or(id) from ct where id<3;
bit_or
--------
3
(1 row)
string_agg:
多行数据字符串拼接:postgres=# select string_agg(attribute,',') from ct;
string_agg
-----------------------------------------
att1,att2,att3,att4,att1,att2,att3,att4
(1 row)
json_agg,xmlagg:
xmlagg需要在编译安装postgresql的时候“HINT: You need to rebuild PostgreSQL using --with-libxml.”。
postgres=# select json_agg(value) from ct;
json_agg
------------------------------------------------------------------
["val1", "val2", "val3", "val4", "val5", "val6", "val7", "val8"]
(1 row)
postgres=# select pg_typeof(json_agg(value)) from ct;
pg_typeof
-----------
json
(1 row)