Re: Array intersection

On Wed, 17 Oct 2007 10:04:21 -0700
David Fetter <david@fetter.org> wrote:

<snip>

> 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
> );
> $$;

Doesn't appear to work on 8.1:

psql=> select array_intersect('{1,2,3}', '{2,3,4}');
ERROR:  could not determine anyarray/anyelement type because input
has type "unknown"

Hopefully upgrading to 8.3 soon so I'll have another look then.

Thanks,

Josh