Re: Array intersection

On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin <josh@trutwins.homeip.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