PostgreSQL 11.1 中LATERAL JOIN子查询嵌入DELETE触发位置语法错误

报错的核心原因是PostgreSQL 11.1 的语法解析规则不允许在 UPDATE 语句的 FROM 子句内的 LATERAL 子查询块中嵌套执行 DELETE 这类写操作语句。

触发规则说明 #

• LATERAL 子句的定位是依附外部查询行上下文的行集生成器，仅支持返回结果集的只读SELECT逻辑，作用是给外部主查询提供关联匹配的数据源，本身不支持承载可修改数据的写操作逻辑。
• DELETE/UPDATE/INSERT 属于独立的顶层DML命令，无法被嵌套在其他查询的数据源子块中执行。就算你给嵌套逻辑套了WITH CTE结构、搭配了RETURNING子句，只要子查询块的最终落点是写操作，就完全不符合PG的语法树解析要求。
• 当前代码的结构，是在UPDATE dx d的FROM子句下，把带DELETE的逻辑作为第二个LATERAL关联源，相当于试图在UPDATE的数据源扫描阶段嵌入删除执行逻辑，解析器扫描到DELETE关键字时找不到合法的语法匹配位置，就直接抛出了位置在DELETE的语法错误。

合规改造方向 #

• 不要尝试在单条UPDATE语句中嵌套删除逻辑，把操作拆为两个独立执行的DML步骤：
  1. 先单独执行DELETE语句：把原代码中嵌套在LATERAL里的CTE逻辑抽出来，作为DELETE语句自身的WITH公共表，正常完成冲突记录的删除操作，可搭配临时表暂存删除涉及的recid集合
  2. 再执行UPDATE语句：把第一步暂存的recid集合作为过滤条件，匹配原UPDATE语句的业务逻辑完成更新，从根源上避免把写操作嵌在查询数据源里。
• 该限制是PostgreSQL从设计层面明确禁止的语法规则，不会随版本升级开放，所有写操作始终需要作为顶层独立语句执行。

内容的提问来源于stack exchange，提问作者Alan Wayne