Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx wrote:


----- Original Message -----
From: "Postgres User" <postgres.developer@xxxxxxxxx>
To: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Friday, May 29, 2009 12:21:11 AM GMT -08:00 Tijuana / Baja California
Subject: 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:

I've got some examples of pulling data ouf of arrays here:




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux