From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Split the result of a query in 2 rows |
Date: | 2015-03-04 19:00:48 |
Message-ID: | 1425495648501-5840509.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Frank Pinto wrote
> So:
> If the original result set returned 1 row with 2 columns the new solution
> would return 2 rows with 1 column?
> If the original result set returned 100 row with 3 columns the new
> solution
> would return 300 rows (1 row gets turned into 3 rows * 100 rows = 300)?
>
> I would use unnest
> <http://blog.lerner.co.il/turning-postgresql-arrays-rows-unnest/>.
> Something like this (untested):
>
> WITH temp_table AS (
> SELECT ARRAY(SELECT field1, field2 FROM tbl_table ORDER BY field1) AS
> prepared_fields;
> )
> SELECT UNNEST(prepared_fields) FROM temp_table;
>
> Note that's using one query using a CTE (
> http://www.postgresql.org/docs/9.3/static/queries-with.html)
>
> Frank
As noted, I'm pretty sure your query will not work as written but it did
inspire the correct solution:
SELECT unnest(ARRAY[f1, f2])
FROM (VALUES (1,2), (3,4)) f (f1, f2)
Note the difference between: ARRAY[val, val] and ARRAY(subquery)
David J.
--
View this message in context: http://postgresql.nabble.com/Split-the-result-of-a-query-in-2-rows-tp5840497p5840509.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Pinto | 2015-03-04 19:06:41 | Re: Split the result of a query in 2 rows |
Previous Message | David G Johnston | 2015-03-04 18:56:14 | Re: Split the result of a query in 2 rows |