Re: lateral function as a subquery - WIP patch

On Fri, Mar 9, 2012 at 8:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Antonin Houska <antonin(dot)houska(at)gmail(dot)com> writes:
>> For example:
>
>> SELECT *
>> FROM  a, b, func(a.i, b.j) as c, d
>> WHERE a.i=b.j and b.j = d.k and c>1
>
>> may become
>
>> SELECT *
>> FROM  a, b, <subquery> as c, d
>> WHERE a.i=b.j and b.j = d.k and c>1
>
>> where <subquery> is
>
>> SELECT func(a.i, b.j)
>> FROM a,b
>> WHERE a.i=b.j
>
> Um ... how do you get the subquery result rows to join to only the
> correct rows of the other tables?  This looks like an unconstrained join
> to me, which is not what I believe the SQL spec for LATERAL to be, and
> it doesn't seem especially useful either.  (If a subquery could do what
> people wanted, we'd not be hearing all the requests for LATERAL.)

I think LATERAL is intended as more or less an unconstrained nested
loop with the lateral expression on the inner side, parameterized by
value from the outer side. Typically it's a SRF.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company