如何复用PostgreSQL中使用WITH关键字声明的CTE临时变量？

WITH 子句定义的**公共表表达式（CTE）**的作用域仅局限于紧随其后的第一条独立SQL语句。你当前的写法拆分了3条独立执行的语句：

1. 第一条带WITH的DELETE语句执行完成后，person_ids临时结果集就会被立即销毁
2. 第二条独立的DELETE语句执行时，已经找不到之前定义的person_ids，因此抛出relation "person_ids" does not exist报错

你需要保证两个删除操作的一致性，同时能复用待删除的人员ID列表，推荐以下两种实现方式：

方案1：使用可写CTE（PostgreSQL支持，原子性更强） #

将两个删除操作合并到同一条SQL语句中，共享同一个CTE的作用域，整个操作天然具备事务原子性，要么全部成功要么全部回滚，完美解决一致性问题：

WITH person_ids AS(
    select person_id from application_person 
    where application_id in (
        select DISTINCT duplicate.id 
        from application duplicate 
        inner join application application on duplicate.document_id = application.document_id
        where duplicate.modify_date < application.modify_date
    )
),
-- 第一个删除操作作为CTE的一部分
del_app_person AS (
    delete from application_person where person_id in (select person_id from person_ids)
)
-- 第二个删除操作作为主语句执行
delete from person where id in (select person_id from person_ids);

方案2：使用临时表（兼容更多数据库） #

如果使用的数据库不支持可写CTE，可以通过临时表存储待删除的ID，配合事务保证一致性：

-- 开启事务
BEGIN;
-- 创建临时表存储待删除ID，事务提交后自动删除临时表
CREATE TEMP TABLE temp_person_ids ON COMMIT DROP AS
select person_id from application_person 
where application_id in (
    select DISTINCT duplicate.id 
    from application duplicate 
    inner join application application on duplicate.document_id = application.document_id
    where duplicate.modify_date < application.modify_date
);

-- 按顺序执行删除
delete from application_person where person_id in (select person_id from temp_person_ids);
delete from person where id in (select person_id from temp_person_ids);

-- 提交事务，所有操作同时生效，临时表自动销毁
COMMIT;

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