Re: Array intersection

On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:

> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.

nm - I just wrote a function - though curious if this is the most
effecient way:

CREATE OR REPLACE FUNCTION array_has_intersect (array1 INTEGER[],
array2 INTEGER[]) RETURNS BOOLEAN
AS $$
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN FALSE;
END IF;

FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
FOR j IN ARRAY_LOWER(array2,1) .. ARRAY_UPPER(array2,1) LOOP
IF (array1[i] = array2[j]) THEN
RETURN TRUE;
END IF;
END LOOP;
END LOOP;

RETURN FALSE;
END;
$$ LANGUAGE PLPGSQL;

psql=> select array_has_intersect('{1,2,3}', '{1,3,4}');
array_has_intersect
---------------------
t

psql=> select array_has_intersect('{1,2,3}', '{21,23,24}');
array_has_intersect
---------------------
f

It doesn't return the actual intersection, but could easily be
modified to do so.

Josh