Re: Nested query performance issue

On Thu, 9 Apr 2009, tiv00 wrote:

> create or replace function explode_array(in_array anyarray) returns setof anyelement as
> $$
>     select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
> $$
> language sql immutable;

Note that you can make this function a bit more general by using
array_lower as the bottom bound:

create or replace function explode_array(in_array anyarray) returns setof anyelement as
$$
      select ($1)[s] from generate_series
        (array_lower($1, 1), array_upper($1, 1)) as s;
$$
language sql immutable;

While you won't run into them in most situations, it is possible to create
arrays where the lower bound isn't 1 by using the subscript syntax.  The
example in the manual even shows that somewhat odd possibilities like
assigning something to "myarray[-2:7]" works.

As already pointed out, once you're in 8.4 the windowing functions might
be a better fit here, but 8.4 does have "unnest" built-in that replaces
the need to code this sort of thing yourself.  You might want to name this
function accordingly to match that upcoming standard (or not, depending on
whether you want to avoid or be reminding of the potential for using the
built-in).  See
http://www.depesz.com/index.php/2008/11/14/waiting-for-84-array-aggregate-and-array-unpacker/
for some examples.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD