I've found a discussion on the PostgreSQL dev list: 1) https://www.postgresql.org/message-id/21317.1385046473%40sss.pgh.pa.us Tom Lane >SQL99 has single-argument UNNEST() but not TABLE(), so why'd they add TABLE() later, and why'd they make it a strict subset of what UNNEST() can do?
2) https://www.postgresql.org/message-id/29437.1386035763%40sss.pgh.pa.us Noah Misch <noah(at)leadboat(dot)com> writes: > That's how I read it, too. My hypothesis is that the standard adopted TABLE() > to rubber-stamp Oracle's traditional name for UNNEST(). 3) finally PostgreSQL settled on "rows from" https://www.postgresql.org/message-id/27951.1386300353%40sss.pgh.pa.us That "rows from" allows left correlation (it is implicitly lateral) test 9.6=> select * from generate_series(1,2) a(v), rows from(generate_series(a.v+10, a.v+11)) as b(v) ; v | v ---+---- 1 | 11 1 | 12 2 | 12 2 | 13 (4 rows) "table functions" from within "unnest" do not work in PostgreSQL: test 9.6=> select * from generate_series(1,2) a(v), unnest(generate_series(a.v+10, a.v+11)) as b(v) ; ERROR: function unnest(integer) does not exist LINE 1: select * from generate_series(1,2) a(v), unnest(generate_ser... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Vladimir вт, 12 сент. 2017 г. в 17:33, Vladimir Sitnikov <[email protected] >: > Hi, > > org.apache.calcite.test.JdbcTest#testUnnestArrayColumn says > "Per SQL std, UNNEST is implicitly LATERAL" > > Currently lateral table functions are cumbersome to write: > select * from main m, lateral (select ... from > table(tableFunction(m.col))).. > > I know table(...) in Oracle is implicitly lateral since Oracle 9i (since > ~2003?) even though "lateral" support was first officially published only > in Oracle 12.1.0.1. > > > I wonder if we can make "table(...)" implicitly lateral as well, so the > syntax will become > select * from main m, table(tableFunction(m.col)) ... > > > PS. I've no idea how to check if that matches the spec or not. > > Vladimir >
