Using the same SELECT statement in two different DELETE statements-CodePudding

How can I factor out the common SELECT statement in the following function?

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;

Actually the second DELETE statement won't work, because the first one will delete all rows with cids from comment_tree_path table and as a result the second SELECT will be empty.

CodePudding user response：

You can use a 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);