LATERAL subqueries - coming to a PostgreSQL release near you ...

Monday, August 6. 2012

LATERAL subqueries - coming to a PostgreSQL release near you ...

Tom Lane has just posted a message that begins thus to the hackers list:

I've been idly amusing myself by trying to hack up support for
SQL-standard LATERAL subqueries. I've got something that turns over,
more or less:

regression=# select * from int4_tbl a, lateral (select unique1,unique2 from tenk1 b where a.f1 = unique1) x;
 f1 | unique1 | unique2 
----+---------+---------
  0 |       0 |    9998
(1 row)

regression=# explain select * from int4_tbl a, lateral (select unique1,unique2 from tenk1 b where a.f1 = unique1) x;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..42.55 rows=5 width=12)
   ->  Seq Scan on int4_tbl a  (cost=0.00..1.05 rows=5 width=4)
   ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..8.28 rows=1 width=8)
         Index Cond: (a.f1 = unique1)
(4 rows)

This is just fantastic. I have lost count of the number of times I have wanted this feature. PostgreSQL just keeps getting better.

Posted by Andrew Dunstan in PostgreSQL at 02:57 | Comments (5) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Andrew, 
 
I'm guessing this is similar to SQL Server's CROSS APPLY and OUTER APPLY constructs. 
 
http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/ 
 
The CROSS APPLY is easy to code around in PostgreSQL by just throwing the call in the SELECT part. The OUTER APPLY is a bit trickier. 
 
Is there a mechanism with this to make that distinction. The OUTER APPLY parallel is really the only construct I miss in PostgreSQL that I had in SQL Server.

#1 Regina (Homepage) on 2012-08-06 05:27 (Reply)

Yes, the main usecase of LATERAL seems to be the same as the one for OUTER APPLY. Join with a set returning function and using columns from the joined table as parameter. 
 
Examples with how I think the new syntax will work: 
 
SELECT ** FROM Department D, 
LATEAL dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 
 
SELECT * FROM Department D 
LEFT JOIN LATERAL dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) ON true

#1.1 Andreas on 2012-08-06 13:47 (Reply)

Very nice for sql code organization. That should make convenient for 'set getter' functions that need to be embedded within other queries to be re-used. 
 
The link from post #1 had an embedded br tag: 
http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

#2 Taras Mitran on 2012-08-06 16:29 (Reply)

I'm confused about what you could do with this tool that you can't already do with joins and/or common table expressions. Is it just a more performant way of doing things?

#3 Jeremy on 2012-08-06 20:32 (Reply)

Say you have a set returning function as one item in your FROM clause. Currently, you can't use a value from one of the other items as an argument to the SRF. i.e. you can't do 
 
select * from mytable as a, myfunc(a.x) as b 
 
LATERAL subqueries would let you do that sort of thing.

#3.1 Andrew Dunstan on 2012-08-06 20:48 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. Wiki format allowed
	Remember Information? Subscribe to this entry