随着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 [ * ]
AI 代码解读
当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;
AI 代码解读
