Re: WIP patch for LATERAL subqueries

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Robert Haas
Sent: Monday, August 06, 2012 6:16 PM
On Sun, Aug 5, 2012 at 5:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.

With Regards,
Amit Kapila.