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