Re: [GENERAL] Array intersection

[Pavel Stehule]

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

nice :)

Maybe we can add function "generate_iterator"

CREATE OR REPLACE FUNCTION generate_iterator(ANYARRAY)
RETURNS SETOF integer AS
$$
   SELECT i
      FROM generate_series(array_lower($1, 1), array_upper($1,1)) AS i
$$ LANGUAGE SQL;

then
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL AS
$$
  SELECT ARRAY(
                           SELECT $1[i]
                               FROM genarate_iterator($1) i
                           INTERSECT
                           SELECT $2[j]
                               FROM generate_iterator($2) j
                           )
$$ ;

Regars
Pavel

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend