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