Postgres-函数返回2个数组的交集？ [英] Postgres - Function to return the intersection of 2 ARRAYs?

问题描述

在postgresql中，您可以使用&&如果两个数组具有相同的成员，即它们重叠，则运算符返回t（true）。有没有可以返回那些普通成员的函数/运算符？

In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are?

即像这样的

select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]);
ARRAY[2]

推荐答案

从8.4开始， Postgres中有用的内置程序，它们使第一个答案的功能更容易甚至更快（这是EXPLAIN告诉我的：对于此查询，（cost = 0.00..0.07行= 1宽度= 64）与（cost = 0.00。 .60.02行= 1宽度= 64）代表原始的一个。）

Since 8.4, there are useful builtins in Postgres which make the function from the first answer easier and possibly faster (that's what EXPLAIN tells me, anyway: "(cost=0.00..0.07 rows=1 width=64)" for this query vs. "(cost=0.00..60.02 rows=1 width=64)" for the original one).

简化的代码是：

SELECT ARRAY
    (
        SELECT UNNEST(a1)
        INTERSECT
        SELECT UNNEST(a2)
    )
FROM  (
        SELECT  array['two', 'four', 'six'] AS a1
              , array['four', 'six', 'eight'] AS a2
      ) q;

是的，您可以将其转换为函数：

and yeah, you can turn it into a function:

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

您可以将其称为

SELECT array_intersect(array['two', 'four', 'six']
                     , array['four', 'six', 'eight']);

但是您也可以将其称为内联：

But you can just as well call it inline too:

 SELECT array(select unnest(array['two', 'four', 'six']) intersect
              select unnest(array['four', 'six', 'eight']));

这篇关于Postgres-函数返回2个数组的交集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！