你能引用同一个递归查询两次吗

Question

我正在尝试递归删除一个文件夹，我得到了以下脚本：

with recursive all_folders (id) as (
select f.id
from folder f
where f.id = 33
union all
select f.id
from folder f
join all_folders of on of.id = f.parent_folder_id
)
delete
from sample
where sample.parent_folder_id in (select id from all_folders);
with recursive all_folders (id) as (
select f.id
from folder f
where f.id = 33
union all
select f.id
from folder f
join all_folders of on of.id = f.parent_folder_id
)
delete
from folder
where folder.id in (select id from all_folders);

---

我想知道是否可以通过引用单个all_folders结果而不是生成两次来清除这种重复。

我还想知道这个查询是否效率低下，或者可以改进。

有什么想法吗？

Answer 1

如果您想在多个查询中使用相同的CTE，那么最好的解决方案很可能是一个临时表。您可以用CTE的结果填充它，并在几个查询中使用它。

例如：

CREATE TEMP TABLE allfolders (
id bigint  -- I hope
);
with recursive all_folders (id) as (
select f.id
from folder f
where f.id = 33
union all
select f.id
from folder f
join all_folders of on of.id = f.parent_folder_id
)
INSERT INTO allfolders (id)
SELEST id FROM allfolders;
delete
from sample
where sample.parent_folder_id in (select id from allfolders);
delete
from folder
where folder.id in (select id from allfolders);
DROP TABLE allfolders;