Re: Note on scalar subquery syntax
От | Kevin Murphy |
---|---|
Тема | Re: Note on scalar subquery syntax |
Дата | |
Msg-id | 42F10F23.70203@genome.chop.edu обсуждение исходный текст |
Ответ на | Re: Note on scalar subquery syntax (Peter Fein <pfein@pobox.com>) |
Ответы |
Re: Note on scalar subquery syntax
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: