Using the same SELECT statement in two different DELETE statements

Question

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;

Answer 1

Use SELECT array_agg(...) INTO to pull the list of targets into a local array variable, then pass that as a parameter to your DELETEs using = ANY:

CREATE OR REPLACE PROCEDURE delete_comment(IN cid integer[])
LANGUAGE plpgsql
AS $procedure$
declare
        descendants_to_delete integer[];
begin
        select array_agg(descendant) into descendants_to_delete
        from comment_tree_path where ancestor = ANY(cid);

        delete from comment_tree_path
        where descendant = ANY(descendants_to_delete);
        delete from comment
        where comment_id = ANY(descendants_to_delete);
end;
$procedure$

Answer 2

You could also use a CTE:

WITH 
  descendants AS
  ( SELECT descendant 
    FROM comment_tree_path 
    WHERE ancestor = ANY(cid)
  ),
  del_ctp AS
  ( DELETE FROM comment_tree_path
    WHERE descendant IN (TABLE descendants)
  )
DELETE FROM comment 
WHERE comment_id IN (TABLE descendants) ;