在两个不同的DELETE语句中使用相同的SELECT语句

Question

如何在以下函数中提出常见的SELECT语句?

CREATE OR REPLACE PROCEDURE delete_comment(cid integer[]) AS $$
BEGIN
DELETE FROM comment_tree_path
WHERE descendant IN (SELECT descendant 
FROM comment_tree_path 
WHERE ancestor = ANY(cid));
DELETE FROM comment 
WHERE comment_id IN (SELECT descendant 
FROM comment_tree_path 
WHERE ancestor = ANY(cid));
END;
$$ LANGUAGE plpgsql;

实际上第二个DELETE语句将不起作用，因为第一个语句将从comment_tree_path表中删除所有具有cids的行，因此第二个SELECT将为空。

Answer 1

您可以使用CTE:

with descendants as (
SELECT descendant 
FROM comment_tree_path 
WHERE ancestor = ANY(cid)
), delete_tree_path as (
DELETE FROM comment_tree_path
WHERE descendant IN (select descendant from descendants)
)
DELETE FROM comment 
WHERE comment_id IN (select descendant from descendants);