Re: Array intersection

On Wed, 17 Oct 2007 17:08:06 +0100
Sam Mason <sam@samason.me.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