Postgres CTE表达式的生命周期是怎样的？为何后续无法调用已定义的CTE？

问题原因 #

WITH子句定义的普通CTE（通用表达式）的作用域仅限其所在的单个SQL语句。你在INSERT语句中定义的cte_list，会在INSERT语句执行完成后自动失效，后续独立执行的DELETE语句属于另一个SQL上下文，自然会报关系不存在的错误。CTE本身支持在同一个SQL语句内多次调用，并不是只能调用一次。

解决方案 #

方案1：单语句多操作CTE（适用于PostgreSQL、逻辑简单的场景） #

PostgreSQL支持可写CTE，你可以把归档、删除等所有操作都放在同一个WITH语句中，共享同一个cte_list定义，示例代码如下：

WITH cte_list AS (
    SELECT pl.col_val FROM prune_list pl 
    JOIN employees.employee emp ON pl.col_val::uuid = emp.id
    WHERE pl.col_nm = 'employee_ref_id' LIMIT 100
),
insert_arch AS (
    INSERT INTO employees.employee_arch_test
    SELECT * FROM employees.employee 
    WHERE id IN (SELECT col_val::uuid FROM cte_list)
    AND NOT EXISTS (
        SELECT 1 FROM employees.employee_arch_test eat 
        WHERE eat.id = employees.employee.id
    )
)
DELETE FROM employees.employee 
WHERE id IN (SELECT col_val::uuid FROM cte_list);

方案2：临时表存储待操作ID（适用于多表操作、循环批量处理场景） #

如果需要在多个独立SQL中复用ID列表，或者后续还要对2-3张表执行相同操作，推荐创建临时表存储结果，临时表在当前数据库会话生效，可反复调用，调试也更方便，示例代码如下：

-- 1. 创建临时表存储待处理的员工ID
CREATE TEMP TABLE temp_operate_emp_ids AS
SELECT pl.col_val::uuid AS emp_id 
FROM prune_list pl 
JOIN employees.employee emp ON pl.col_val::uuid = emp.id
WHERE pl.col_nm = 'employee_ref_id' LIMIT 100;

-- 2. 执行归档插入
INSERT INTO employees.employee_arch_test
SELECT * FROM employees.employee 
WHERE id IN (SELECT emp_id FROM temp_operate_emp_ids)
AND NOT EXISTS (
    SELECT 1 FROM employees.employee_arch_test eat 
    WHERE eat.id = employees.employee.id
);

-- 3. 执行主表删除
DELETE FROM employees.employee 
WHERE id IN (SELECT emp_id FROM temp_operate_emp_ids);

-- 4. 后续操作其他关联表时可以继续复用temp_operate_emp_ids
-- 临时表会在会话结束后自动清理，也可以手动删除
DROP TABLE IF EXISTS temp_operate_emp_ids;

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