On Sun, Aug 5, 2012 at 5:58 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > I've been idly amusing myself by trying to hack up support for > SQL-standard LATERAL subqueries.
Cool! > Currently the patch only implements the syntax called out in the standard, > namely that you can put LATERAL in front of a <derived table>, which is > to say a parenthesized sub-SELECT in FROM. It strikes me that it might be > worth allowing LATERAL with a function-in-FROM as well. So basically > LATERAL func(args) <alias> > would be an allowed abbreviation for > LATERAL (SELECT * FROM func(args)) <alias> > Since the standard doesn't have function-in-FROM, it has nothing to say > about whether this is sane or not. The argument for this is mainly that > SRFs are one of the main use-cases for LATERAL (replacing SRF-in-the- > SELECT-list usages), so we might as well make it convenient. Any opinions > pro or con about that? Apparently Sybase and Microsoft SQL server use a slightly different syntax, CROSS APPLY, for this. http://iablog.sybase.com/paulley/2008/07/cross-and-outer-apply/ It may make sense to consider mimicking that instead of inventing our own way of doing it, but I haven't investigated much so it's also possible that it doesn't make sense. > While fooling around in the planner I realized that I have no idea what > outer-level aggregates mean in a LATERAL subquery, and neither does > Postgres: > regression=# select 1 from tenk1 a, lateral (select * from int4_tbl b where > f1 = max(a.unique1)) x; > ERROR: plan should not reference subplan's variable > I don't see anything prohibiting this in SQL:2008, but ordinarily this > would be taken to be an outer-level aggregate, and surely that is not > sensible in the LATERAL subquery. For the moment it seems like a good > idea to disallow it, though I am not sure where is a convenient place > to test for such things. Has anyone got a clue about whether this is > well-defined, or is it simply an oversight in the spec? My mental picture of LATERAL (which might be inaccurate) is that it has the semantics that you'd get from a parameterized nestloop. So I can't assign any meaning to that either. > Comments, better ideas? Thanks for working on this - sorry I don't have more thoughts right at the moment. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
