PostgreSQL’s New LATERAL Join Type

Re:PostgreSQL’s New LATERAL Join Type by ajw0100 on Wednesday December 03, 2014 @04:39AM (#8692731)

What if the limits on the lateral subqueries were 2 instead of 1, and they were doing select * instead on select sum() in the outer query? How would you recreate that with correlated SCALAR subqueries? There's no such thing as non-scalar correlated subqueries is there?

[ Parent | on HN ]

Re:PostgreSQL’s New LATERAL Join Type by nunwuo on Wednesday December 03, 2014 @05:45AM (#8692955)

Untested, but this is the general approach:

  SELECT unnest(ar).* FROM
    (SELECT ARRAY(SELECT tbl FROM tbl
                  WHERE .. ORDER BY .. LIMIT 2) AS ar
     FROM .. OFFSET 0) ss;

If you want a specific set of columns instead of *, you'd need to create a custom composite type to create an array of, since it's not possible to "unpack" anonymous records.

[ Parent | on HN ]