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

1

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.

answered Aug 28, 2014 at 18:11

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - return multiple rows as one

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related