Re: Array intersection

On Wed, Oct 17, 2007 at 11:28:31AM -0500, Josh Trutwin wrote:
> It's inelegant, but I just did this:

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

humm, why didn't that seem to work for me... ah well. Next version
fixes a problem that I didn't test of the inputs being NULL. '[]' isn't
semantically correct, let alone the correct syntax. I've also fixed the
problem with items in the first array appearing twice. Try:

CREATE OR REPLACE FUNCTION array_intersect (array1 INTEGER[],array2 INTEGER[]) RETURNS INTEGER[]
AS $$
DECLARE
out INTEGER[];
BEGIN
out := '{}'::INTEGER[];
IF array1 IS NULL OR array2 IS NULL THEN
RETURN NULL;
END IF;
FOR i IN array_lower(array1,1) .. array_upper(array1,1) LOOP
IF (array1[i] = ANY (array2)) AND NOT array1[i] = ANY (out) THEN
out := array_append(out,array1[i]);
END IF;
END LOOP;
RETURN out;
END;
$$ LANGUAGE PLPGSQL;

Sam