News for hackers. Stuff that matters.
Re:PostgreSQL’s New LATERAL Join Type by nunwuo on Tuesday December 02, 2014 @05:12PM (#8690326)
That's not true. Anything you can do with LATERAL you can also do with correlated scalar subqueries in the SELECT list. LATERAL simply makes writing these kinds of queries easier and more intuitive.
Re:PostgreSQL’s New LATERAL Join Type by bchallenor on Tuesday December 02, 2014 @05:22PM (#8690396)
The syntax for this is pretty horrible, however. And if you want to return more than one column from the subquery, you would have to duplicate the subquery definition for each column, right? Then you'd have to have faith that the optimizer can work out what you meant and reconstruct just a single subquery.
Re:PostgreSQL’s New LATERAL Join Type by nunwuo on Tuesday December 02, 2014 @05:45PM (#8690549)
There's no faith required; the planner is guaranteed not to do that. The "normal" way is to create a composite type containing each of the columns you need, and then "unpack" it to separate columns. Horrible? Yeah, but it's possible.
Re:PostgreSQL’s New LATERAL Join Type by bchallenor on Tuesday December 02, 2014 @06:10PM (#8690725)
Is it possible with scalar subqueries to perform anything other than a tree of correlation? With CROSS APPLY one can correlate a DAG of subqueries, e.g. a diamond where B and C depend on A, and D depends on B and C.
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?
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.