Re: How to explode an array into multiple rows

Kevin Murphy <murphy2@speakeasy.net> writes:
> I'd like to do something like this:
> select array_explode(array_col) from table1 where col2 = 'something';
> where array_explode returns the elements of array_col unpacked onto
> separate rows.

> I tried creating a function returning a setof type, but postgresql
> produces a "set-valued function called in context that cannot accept a
> set" error.  I've seen this error in the list archives, but I'm not sure
> how to translate the simple cases discussed into this situation.

This is a limitation of the SRF implementation in plpgsql.  You can work
around it in a grotty way by wrapping the plpgsql function inside a sql
function:

regression=# create function explode1(anyarray) returns setof anyelement as
regression-# 'begin
regression'#   for i in array_lower($1, 1) .. array_upper($1, 1) loop
regression'#     return next $1[i];
regression'#   end loop;
regression'#   return;
regression'# end' language plpgsql strict immutable;
CREATE FUNCTION
-- this doesn't work:
regression=# select explode1('{1,2,3,4}'::int[]);
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "explode1" line 3 at return next
-- but this does:
regression=# create function explode(anyarray) returns setof anyelement as
regression-# 'select * from explode1($1)' language sql strict immutable;
CREATE FUNCTION
regression=# select explode('{1,2,3,4}'::int[]);
 explode
---------
       1
       2
       3
       4
(4 rows)

I tested this in PG 8.0.3; not sure if it will work in pre-8.0 releases.
Not sure about the performance, either, but at least it works.

            regards, tom lane