Пересечение нескольких массивов в PostgreSQL

Самое близкое к пересечению массивов, о котором я могу думать, это:

select array_agg(e)
from (
    select unnest(a1)
    intersect
    select unnest(a2)
) as dt(e)

Это предполагает, что a1 и a2 являются одномерными массивами с элементами одного типа. Вы можете обернуть это в функцию примерно так:

create function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
    ret int[];
begin
    -- The reason for the kludgy NULL handling comes later.
    if a1 is null then
        return a2;
    elseif a2 is null then
        return a1;
    end if;
    select array_agg(e) into ret
    from (
        select unnest(a1)
        intersect
        select unnest(a2)
    ) as dt(e);
    return ret;
end;
$$ language plpgsql;

Затем вы можете делать такие вещи:

=> select array_intersect(ARRAY[2,4,6,8,10], ARRAY[1,2,3,4,5,6,7,8,9,10]);
 array_intersect 
-----------------
 {6,2,4,10,8}
(1 row)

Обратите внимание, что это не гарантирует какого-либо определенного порядка в возвращаемом массиве, но вы можете исправить это, если вам это небезразлично. Затем вы можете создать свою собственную агрегатную функцию:

-- Pre-9.1
create aggregate array_intersect_agg(
    sfunc    = array_intersect,
    basetype = int[],
    stype    = int[],
    initcond = NULL
);

-- 9.1+ (AFAIK, I don't have 9.1 handy at the moment
-- see the comments below.
create aggregate array_intersect_agg(int[]) (
    sfunc = array_intersect,
    stype = int[]
);

И теперь мы видим, почему array_intersect делает забавные и несколько неуклюжие вещи с NULL. Нам нужно начальное значение для агрегации, которое ведет себя как универсальный набор, и мы можем использовать для этого NULL (да, это немного пахнет, но я не могу придумать ничего лучше).

Как только все это будет на месте, вы можете сделать следующее:

> select * from stuff;
    a    
---------
 {1,2,3}
 {1,2,3}
 {3,4,5}
(3 rows)

> select array_intersect_agg(a) from stuff;
 array_intersect_agg 
---------------------
 {3}
(1 row)

Не совсем простой или эффективный, но, возможно, разумная отправная точка и лучше, чем вообще ничего.

Полезные ссылки:

• array_agg
• создать агрегат
• создать функцию
• PL/pgSQL
• unnest

Принятый ответ не сработал для меня. Вот как я это исправил.

create or replace function array_intersect(a1 int[], a2 int[]) returns int[] as $$
declare
  ret int[];
begin
  -- RAISE NOTICE 'a1 = %', a1;
  -- RAISE NOTICE 'a2 = %', a2;
  if a1 is null then
    -- RAISE NOTICE 'a1 is null';
    return a2;
  -- elseif a2 is null then
  --    RAISE NOTICE 'a2 is null';
  --    return a1;
  end if;
  if array_length(a1,1) = 0 then
    return '{}'::integer[];
  end if;
  select array_agg(e) into ret
  from (
    select unnest(a1)
    intersect
    select unnest(a2)
  ) as dt(e);
  if ret is null then
    return '{}'::integer[];
  end if;
  return ret;
end;
$$ language plpgsql;

Поздновато отвечать на этот вопрос, но может кому понадобится, поэтому решил поделиться тем, что написал, так как не нашел готового решения для пересечения любого количества массивов. Итак, вот оно. Эта функция получает массив массивов, если это только один массив, функция возвращает первый массив, если есть 2 массива, функция пересекает 2 массива и возвращает результат, если массивов больше 2, функция берет пересечение 2 первых массивов, сохраняет его в некоторой переменной и перебирает все остальные массивы, пересекает каждый следующий массив с сохраненным результатом и сохраняет результат в переменной. если результат равен нулю, он существует с нулевым значением. В переменной и хранится массив с обработанными данными, возвращаемыми функцией.

CREATE OR REPLACE FUNCTION array_intersected(iarray bigint[][])
  RETURNS bigint[] AS
$BODY$
    declare out_arr bigint[]; set1 bigint[]; set2 bigint[];
    BEGIN
        --RAISE NOTICE '%', array_length(iarray, 1);
        if array_length(iarray, 1) = 1 then
            SELECT ARRAY(SELECT unnest(iarray[1:1])) into out_arr;
        elseif array_length( iarray, 1) = 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
        elseif array_length(iarray, 1) > 2 then
            set1 := iarray[1:1];
            set2 := iarray[2:2];
            --exit if no common numbers exists int 2 first arrays
            SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(set2))into out_arr;
            if out_arr = NULL then
                EXIT;
                END IF;
            FOR i IN 3 .. array_upper(iarray, 1)
            LOOP
               set1 := iarray[i:i];
               SELECT ARRAY(SELECT unnest(set1) INTERSECT SELECT unnest(out_arr))into out_arr;
               if out_arr = NULL then
                EXIT;
                   END IF;
            END LOOP;
        end if;

    return out_arr;

    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE;

Вот код, подтверждающий его работу.

select array_intersected(array[[1, 2]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3]]::bigint[][]);

select array_intersected(array[[1, 2],[2, 3], [2, 4]]::bigint[][]);

select array_intersected(array[[1, 2, 3, 4],[null, null, 4, 3], [3, 1, 4, null]]::bigint[][]);