On Mon, Aug 6, 2012 at 10:07 AM, Amit Kapila <amit.kap...@huawei.com> wrote: >>> 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. > > There are certain differences mentioned in the link due to which I am > not sure it can be mimicked exactly, and may be that's why Sybase also has > both syntaxes. > > Differences > ----------------- > The LATERAL keyword cannot NULL-supply rows, whereas an OUTER APPLY will. > Additionally, with a LATERAL derived table, the derived table and the outer > reference must be separated by a comma. For an APPLY operator, the table > expression on the right and the outer reference cannot be separated by a > comma, but they can be separated by any other join operator. In other words, > the APPLY operator allows references to any table within the left table > expression, whereas the LATERAL keyword allows references to tables outside > the current table expression.
I think you can always simulate CROSS APPLY using LATERAL. The syntax is different but the functionality is the same. However, OUTER APPLY allows you to do something that I don't think is possible using LATERAL. While it would be nice to have both CROSS APPLY and OUTER APPLY, my main point was to suggest supporting CROSS APPLY rather than the extension to the LATERAL syntax Tom proposed. That is, the spec allows: FROM x, LATERAL (SELECT * FROM srf(x.a)) y ...and Tom proposed allowing this to be shortened to: FROM x, LATERAL srf(x.a) ...and what I'm saying is maybe we should instead allow it to be shortened to: FROM x CROSS APPLY srf(x.a) ...as some other database systems are already doing. I can't think of any particular reason why Tom's proposed shorthand would be problematic; I'm just suggesting that it may be better to support the same shorthand that other people already support rather than inventing our own, idiosyncratic shorthand. That having been said, I get paid the same either way. -- 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
