随着ADB PG 6.0对PG内核的升级，ADB PG 6.0中支持了LATERAL语法。

LATERAL语法

在Select语句中，LATERAL关键词可以放置在sub-select的from对象之前，这样在sub-select中，在LATERAL后的对象可以引用其之前对象的列（如果不使用LATERAL，sub-select的各个对象会被单独处理，所以就无法引用from列表中的其它对象）。

LATERAL也可以放置在from列表中函数对象之前，这样在函数中就可以引用在其之前任何from列表中的其它对象。另外，LATERAL对象也可以出现在最外层的from列表中。

具体语法使用如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ]( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

当from列表中某个LATERAL对象有对其它对象的引用时，查询处理流程如下：对于被引用对象的每一行，LATERAL对象会使用改行做查询处理，处理完得到的结果会与源对象的行做join。因此，源对象中的列必须是和LATERAL对象做INNER或者LEFT join，因为尽管X RIGHT JOIN LATERAL Y在语法层面是有效的，但是实际上Y来引用X是不行的（因为需要从源表展开）。

示例：

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

create table test (c1 int, c2 int, c3 int);
insert into test values (1,1,1),(1,2,3),(1,3,3),(2,2,2),(2,3,2),(2,4,2),(3,4,1),(3,4,2);

create table teest1 (c1 int, c2 int);
insert into test1 values (1,2),(2,3),(3,4);

select test1.c1 from test1 left join LATERAL (select * from test where test1.c1 = test.c1) tt on true;

with t1 as (select c1, avg(c2) as avg_c2 from test group by c1)
select tbl.c1, tbl.c2, tbl.c3, t1.avg_c2 from test tbl, t1 where tbl.c1 = t1.c1;

上面的with语句可以等价写成下面LATERAL语句

select test.*, t1.avg_c2 from test, lateral (select c1, avg(c2) as avg_c2 from test group by c1) t1 where test.c1 = t1.c1;

参考资料

https://www.postgresql.org/docs/9.4/sql-select.html