Re: Array intersection

On Wed, Oct 17, 2007 at 10:19:43AM -0500, Josh Trutwin wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.

You can use an SQL function, which has the advantage of always being
available :)

It's up to you to ensure that the input arrays have the same type.

Cheers,
David.

CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[i] AS "the_intersection"
    FROM generate_series(
        array_lower($1,1),
        array_upper($1,1)
    ) AS i
    INTERSECT
    SELECT $2[j] AS "the_intersection"
    FROM generate_series(
        array_lower($2,1),
        array_upper($2,1)
    ) AS j
);
$$;
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate