Re: How to explode an array into multiple rows

Kevin Murphy <murphy2(at)speakeasy(dot)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