1

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;
1
  • Please do not crosspost
    – user1822
    Commented Nov 23, 2022 at 6:40

2 Answers 2

3

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$
2
4

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) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.