Re: Reference to parent query from ANY sublink

Kevin Grittner <kgrittn@ymail.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