From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com>, Antonin Houska <antonin(dot)houska(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Reference to parent query from ANY sublink |
Date: | 2013-12-06 21:04:10 |
Message-ID: | 1386363850.87722.YahooMailNeo@web162902.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> test=# SELECT *
> FROM tab1 a
> LEFT JOIN
> tab2 b
> ON a.i = ANY (
> SELECT k
> FROM tab3 c
> WHERE k = a.i);
> i | j
> ---+---
> 1 | 4
> 1 | 5
> 1 | 6
> 2 |
> 3 | 4
> 3 | 5
> 3 | 6
> (7 rows)
>
>> SELECT *
>> FROM tab1 a
>> LEFT JOIN
>> (
>> SELECT *
>> tab2 b
>> SEMI JOIN
>> ( SELECT k
>> FROM tab3 c
>> WHERE k = a.i
>> ) AS ANY_subquery
>> ON a.i = ANY_subquery.k
>> ) AS SJ_subquery
>> ON true;
>
> It is hard to see what you intend here
Perhaps you were looking for a way to formulate it something like
this?:
test=# SELECT *
test-# FROM tab1 a
test-# LEFT JOIN LATERAL
test-# (
test(# SELECT *
test(# FROM tab2 b
test(# WHERE EXISTS
test(# (
test(# SELECT *
test(# FROM tab3 c
test(# WHERE c.k = a.i
test(# )
test(# ) AS SJ_subquery
test-# ON true;
i | j
---+---
1 | 4
1 | 5
1 | 6
2 |
3 | 4
3 | 5
3 | 6
(7 rows)
Without LATERAL you get an error:
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 11: WHERE c.k = a.i
^
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2013-12-06 21:06:55 | Re: WITHIN GROUP patch |
Previous Message | Tom Lane | 2013-12-06 20:24:13 | Re: pg_stat_statements: calls under-estimation propagation |