邮政 CTE 表达式的"lifespan"是什么？例如...如

Question

我有一个CTE，我使用它从两个表中提取一些数据，然后粘贴在一个名为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
)

然后，我正在做一个插入，将记录从cte_list移动到另一个名为employee_arch_test的存档表(如果它们不存在)

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 where employees.employee_arch_test.id=employees.employee.id)
);

这似乎工作良好。问题是当我添加另一个语句后，做一些删除从主employee表使用上述cte_list- cte_list显然不再存在?

SQL Error [42P01]: ERROR: relation "cte_list" does not exist

实际的删除查询:

delete from employees.employee where id in (select col_val::uuid from cte_list);

cte_listCTE表只能使用一次吗?我在LOOP中运行这些语句，我需要对大约2或3个其他表运行完全相同的调用，但在这里遇到了一个关键点。

Answer 1

CTE仅在它所属的语句期间存在。我猜您有一个INSERT语句，前面有CTE:

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 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
where employees.employee_arch_test.id = employees.employee.id)
);

CTE是的部分INSERT语句——它本身并不是一个单独的语句。它只在INSERT语句执行期间存在。

如果你需要更持久的东西，你的选择是:

1. 将相同的CTE添加到以下每个语句中。请注意，由于数据库中的数据可能会更改，因此每次调用CTE都可能返回不同的数据。
2. 创建一个执行与CTE相同操作的视图，然后使用该视图代替CTE。请注意，由于数据库中的数据可能会更改，因此视图的每次调用可能会返回不同的数据。
3. 创建一个临时表来保存来自CTE查询的数据，然后使用临时表代替CTE。这样做的优点是为所有操作提供一致的数据集。