On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin <josh@xxxxxxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
