From: | artacus(at)comcast(dot)net |
---|---|
To: | Postgres User <postgres(dot)developer(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Converting each item in array to a query result row |
Date: | 2009-05-29 08:52:32 |
Message-ID: | 61052167.10978411243587152061.JavaMail.root@sz0018a.emeryville.ca.mail.comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
----- 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Gevik Babakhani | 2009-05-29 09:04:55 | Re: Converting each item in array to a query result row |
Previous Message | Postgres User | 2009-05-29 07:21:11 | Converting each item in array to a query result row |