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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
