PostgreSQL Function to return the intersection of 2 ARRAYs

背景

QQ群里面的兄弟问到的一个问题 :

 --查找数组元素的交集  
mydb=> select array[1,2,3] & array[3,4,5];  
 ?column?   
----------  
 {3}  
(1 row)  
网上人家的DEMO里能过去 我本地老提示error   

正文

因为这个操作符没有, 所以报错是必然的.

digoal=# \do &  
                               List of operators  
   Schema   | Name | Left arg type | Right arg type | Result type | Description   
------------+------+---------------+----------------+-------------+-------------  
 pg_catalog | &    | bigint        | bigint         | bigint      | bitwise and  
 pg_catalog | &    | bit           | bit            | bit         | bitwise and  
 pg_catalog | &    | inet          | inet           | inet        | bitwise and  
 pg_catalog | &    | integer       | integer        | integer     | bitwise and  
 pg_catalog | &    | macaddr       | macaddr        | macaddr     | bitwise and  
 pg_catalog | &    | smallint      | smallint       | smallint    | bitwise and  
(6 rows)  

报错 :

digoal=# select array[1,2,3]::int[] & array[3,4,5]::int[];  
ERROR:  operator does not exist: integer[] & integer[]  
LINE 1: select array[1,2,3]::int[] & array[3,4,5]::int[];  
                                   ^  
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.  

这个操作符来自intarray扩展.

digoal=# create extension intarray;  
CREATE EXTENSION  
digoal=# \do &  
                               List of operators  
   Schema   | Name | Left arg type | Right arg type | Result type | Description   
------------+------+---------------+----------------+-------------+-------------  
 pg_catalog | &    | bigint        | bigint         | bigint      | bitwise and  
 pg_catalog | &    | bit           | bit            | bit         | bitwise and  
 pg_catalog | &    | inet          | inet           | inet        | bitwise and  
 pg_catalog | &    | integer       | integer        | integer     | bitwise and  
 pg_catalog | &    | macaddr       | macaddr        | macaddr     | bitwise and  
 pg_catalog | &    | smallint      | smallint       | smallint    | bitwise and  
 public     | &    | integer[]     | integer[]      | integer[]   |   
(7 rows)  

扩展包 :

pgdev@db-172-16-3-150-> cd $PGHOME/share/extension  
intarray--1.0.sql   
CREATE OPERATOR & (  
        LEFTARG = _int4,  
        RIGHTARG = _int4,  
        COMMUTATOR = &,  
        PROCEDURE = _int_inter  
);  

如果要对其他类型的数组执行相交的操作, 可以使用以下函数, 将数组解成record后相交, 再组装.

CREATE FUNCTION array_intersect(anyarray, anyarray)   RETURNS anyarray   language sql as $FUNCTION$     SELECT ARRAY(         SELECT UNNEST($1)         INTERSECT         SELECT UNNEST($2)     ); $FUNCTION$;  

如下 :

digoal=# CREATE FUNCTION array_intersect(anyarray, anyarray)  
digoal-#   RETURNS anyarray  
digoal-#   language sql  
digoal-# as $FUNCTION$  
digoal$#     SELECT ARRAY(  
digoal$#         SELECT UNNEST($1)  
digoal$#         INTERSECT  
digoal$#         SELECT UNNEST($2)  
digoal$#     );  
digoal$# $FUNCTION$;  
CREATE FUNCTION  
digoal=# SELECT array_intersect(array['two', 'four', 'six']  
digoal(#                      , array['four', 'six', 'eight']);  
 array_intersect   
-----------------  
 {four,six}  
(1 row)  

参考

1. http://stackoverflow.com/questions/756871/postgres-function-to-return-the-intersection-of-2-arrays

2. http://www.postgresql.org/docs/current/static/intarray.html

digoal’s 大量PostgreSQL文章入口