Re: Converting each item in array to a query result row

I needed to do this just the other day, here's what I did:

create or replace function explode(_a anyarray) returns setof
anyelement as $$
begin
for i in array_lower(_a,1) .. array_upper(_a,1) loop
return next _a[i];
end loop;
return;
end;
$$ language plpgsql strict immutable;

select * from explode((select col from table where id = whatever)) as
Exploded;

On 29/05/2009, at 6:52 PM, artacus(at)comcast(dot)net wrote:

>
> ----- Original Message -----
> From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
> To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
> Sent: Friday, May 29, 2009 12:21:11 AM GMT -08:00 Tijuana / Baja
> California
> Subject: [GENERAL] Converting each item in array to a query result row
>
> Hi,
>
> I'd writing a query against a function (pg_proc) that contains 2
> fields of an array type. Ideally, I'd like to select 1 row from the
> table, but return a query row for each item in the array.
>
> For example, if one row contains the array {"a", "b", "c"}
> I'd like the query to return 3 rows, one for each of these elements.
>
> Any idea if this is possible?
>
>
> Not sure exactly what you want, but this should get you in the ball
> park
> SELECT f.my_field[i] AS value,
> (array['I am one','I am two','I am three'])[i] AS description
> FROM my_func(foo) f
> JOIN generate_series(1,3) i ON 1=1
>
>
> Postgres 8.4 will has an unpack() function to convert an array to a
> set. Pavel has a write up about doing this in 8.3 and lower here:
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Array_to_table
>
> I've got some examples of pulling data ouf of arrays here:
> http://scottrbailey.wordpress.com/2009/05/20/etl-with-postgres-arrays/
>
>