PostgreSQL - return multiple rows as one

Question

I have a query that returns two rows:

SELECT * FROM ( SELECT cola,colb from table LIMIT 2 ) as f

Result is:

cola, colb x, 1 y, 2

Is it possible to use the results in the top level SELECT? Similar to:

SELECT some_function(x,y), some_other_function(1,2) FROM ( SELECT cola,colb from table LIMIT 2 ) as f

Here is the actual query:

SELECT * FROM ( SELECT alt, st_distance_sphere( st_closestpoint(geom,st_setsrid(st_makepoint(x,y),4326)), st_setsrid(st_makepoint(x,y),4326) ) as d FROM table ) as foo

It returns:

alt | d 800 | 9.658 900 | 11.59 etc

You can't use the values retrieved in the subquery as fieldnames in the parent query. SQL doesn't work that way. You COULD retrieve those values and build a new query from them, but there's no automatic way to do what you want. — Marc B
– Marc B, Commented Aug 28, 2014 at 18:02
I assume it's something similar to cola[0] etc. I haven't been able to see anything about this online (maybe I'm searching for the wrong terminology?) so posted here looking for help... ? — Matt
– Matt, Commented Aug 28, 2014 at 18:03
Hmm...Are the IDs to which x and y, 1 and 2 belong the same? If so, then this could probably be done with a pivot solution. — Jaaz Cole
– Jaaz Cole, Commented Aug 28, 2014 at 18:10
@JaazCole - they are not (I think you are asking if they have a key?). The initial query is actually a PostGIS spatial calculation that uses data passed to the query and not a table. — Matt
– Matt, Commented Aug 28, 2014 at 18:13

Erwin Brandstetter · Accepted Answer · 2014-08-28 18:11:48Z

You need some kind of cross-tabulation to aggregate multiple rows into one.

Your example would work like this:

SELECT some_function(arr_a[1], arr_a[2]) , some_other_function(arr_b[1], arr_b[2]) FROM ( SELECT array_agg(cola) AS arr_a , array_agg(colb) AS arr_b FROM ( SELECT cola, colb FROM tbl ORDER BY cola LIMIT 2 ) sub1 ) sub2;

There are various ways. Depends on your actual problem.

Collectives™ on Stack Overflow

PostgreSQL - return multiple rows as one

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related