Обсуждение: How to explode an array into multiple rows

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.  I'm
sure it's something simple, but it's been eluding me.

Thanks,
Kevin Murphy

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