Re: Array intersection

> This is only going to work for one-dimensional arrays (I'm not sure
> how you would ever fix that with the support postgres has for
> arrays) but the (computational) complexity of having an embedded
> FOR loops looks bad for performance. As you can already use '=ANY'
> syntax to search inside an array, you may as well use that---it's
> probably a bit more faster than the plpgsql work-alike. Leading to
> the following implementation of intersect:

Thanks for the pointers.

> It seems to work for me, but as a side effect will leave the array
> sorted in the same order as the first parameter and with any
> duplicates it has. Even more annoyingly if there is no intersection
> it will return NULL instead of an empty array, how do I fix this?

It's inelegant, but I just did this:

CREATE OR REPLACE FUNCTION array_intersect (array1 INTEGER[],array2
INTEGER[]) RETURNS INTEGER[]
AS $$
DECLARE
out INTEGER[];
return_empty BOOLEAN := TRUE;
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN '[]';
END IF;
FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
IF (array1[i] =ANY (array2)) THEN
out := array_append(out,array1[i]);
return_empty := FALSE;
END IF;
END LOOP;

IF return_empty THEN
RETURN '{}';
END IF;

RETURN out;
END;
$$ LANGUAGE PLPGSQL;

psql=> select array_intersect('{1,2,3}', '{6,7,8}');
array_intersect
-----------------
{}
(1 row)

Josh