Re: Note on scalar subquery syntax

Peter Fein wrote:

>Kevin Murphy wrote:
>
>
>>As an example, I wrote a function to explode, or unpack, the elements of
>>an array onto separate rows (its return type is SETOF whatever), but it
>>took me a while to figure out how to use it effectively in queries.
>>
>>
>
>Mind posting it? I know I've had need of such I thing & IIRC others
>have asked as well...
>
>
I'm no expert, but per Peter's request, here is a generic
array-unpacking function that works in PostgreSQL 8.0. It can't be
invoked if the argument doesn't have an explicit type. I.e. you would
have to use it as: "select * from
array_explode_generic('{apple,banana,cherry}'::text[]);" or "select *
from array_explode_generic('{1,2,3}'::integer[]);".

CREATE OR REPLACE FUNCTION array_explode(an_array anyarray) RETURNS
SETOF anyelement AS $$
DECLARE
idx integer;
BEGIN

FOR idx IN 1 .. ARRAY_UPPER(an_array, 1) LOOP
RETURN NEXT an_array[idx];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

I would imagine that a type-specific version would be faster. For that,
replace "anyarray" with, e.g. "integer[]", and "anyelement" with, e.g.
"integer".

-Kevin Murphy