PL/pgSQL中含DML的WITH子句报错问题及优雅解法问询

在标准SQL中，我们可以在WITH子句中包含多个数据修改语句（DML），示例如下：

WITH 
  common_data_for_ins_and_upd AS (
    SELECT ...
  ),
  ins AS (
    INSERT INTO some_table(...) ...
  ),
  upd AS (
    UPDATE some_table SET ... ...
  )
SELECT 1;

但在PL/pgSQL函数中直接使用该结构时，会遇到以下错误：

• 外层用SELECT会触发 ERROR: query has no destination for result data
• 替换SELECT为PERFORM会报语法错误
• 在外层包裹PERFORM会得到 WITH clause containing a data-modifying statement must be at the top level

现有临时方案 #

目前常见的临时解决办法包括：

• 将最后一个DML语句移出WITH子句：

  WITH 
    ins AS (
      INSERT INTO some_table(...) ...
    )
  UPDATE some_table SET ... ...;
  

• 用无实际操作的DML语句收尾（如DELETE FROM some_table WHERE false）
• 使用SELECT INTO但需声明一个无用变量

优雅解决方案 #

更优雅的方式是使用SELECT ... INTO NULL来丢弃WITH子句的查询结果，无需声明额外变量，示例如下：

WITH 
  common_data_for_ins_and_upd AS (
    SELECT id FROM source_table WHERE status = 'pending'
  ),
  ins AS (
    INSERT INTO target_table(id, created_at) 
    SELECT id, NOW() FROM common_data_for_ins_and_upd
  ),
  upd AS (
    UPDATE source_table SET status = 'processed' 
    WHERE id IN (SELECT id FROM common_data_for_ins_and_upd)
  )
SELECT 1 INTO NULL;

这种方式既保持了WITH子句的结构完整性，又避免了额外的变量声明或结构拆分，完全符合PL/pgSQL的语法要求。

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