Re: Array intersection - Mailing list pgsql-general

From Josh Trutwin
Subject Re: Array intersection
Date
Msg-id 20071017103723.35d99d27@sinkhole.intrcomm.net
Whole thread Raw
In response to Array intersection  (Josh Trutwin <josh@trutwins.homeip.net>)
Responses Re: Array intersection  (Sam Mason <sam@samason.me.uk>)
Re: Array intersection  ("Rodrigo De León" <rdeleonp@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "Albe Laurenz"
Date:
Subject: Re: Prepared statement parameters for an 'IN ()' clause
Next
From: Chris Browne
Date:
Subject: Re: DROP VIEW lasts forever