Re: Array intersection

On Wed, Oct 17, 2007 at 01:06:35PM -0500, Josh Trutwin wrote:
> On Wed, 17 Oct 2007 10:04:21 -0700
> David Fetter <david(at)fetter(dot)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"

As mentioned in the "release notes" ;), it's up to you to ensure that
the arrays have the right types, so you'd write the above as:

SELECT array_intersect('{1,2,3}'::int[], '{2,3,4}'::int[]);

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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