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  wrote:
> 
> 
> 
> > 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  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