Re: Array intersection

On Wed, 17 Oct 2007 17:08:06 +0100
Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
> out INTEGER[];
> 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]);
> END IF;
> END LOOP;
> RETURN out;
> END;
> $$ LANGUAGE PLPGSQL;

Is the =ANY specific to PG 8.2 or higher? On 8.1.10:

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

(1 row)

Also, I think the first return needs to be:

RETURN '{}';

instead of:

RETURN '[]';

Josh