如何提取存储过程中重复SELECT语句？解决DELETE执行顺序失效问题

1. 提取重复SELECT并复用 #

要解决重复代码问题，同时修复第二个DELETE失效的bug，核心思路是先把重复查询的结果预存起来，再让两个DELETE语句复用这个预存结果，避免因第一个DELETE修改表数据导致第二个查询无输出。

方案一：用PL/pgSQL变量存储结果 #

适合数据量不大的场景，用数组变量保存需要处理的ID集合：

CREATE OR REPLACE PROCEDURE delete_comment(cid integer[]) AS $$
DECLARE
    target_ids integer[]; -- 存储待删除的ID集合
BEGIN
    -- 一次性查询出所有需要处理的descendant，存入变量
    SELECT ARRAY_AGG(descendant) INTO target_ids
    FROM comment_tree_path 
    WHERE ancestor = ANY(cid);

    -- 第一个DELETE：复用预存的ID集合
    DELETE FROM comment_tree_path
    WHERE descendant = ANY(target_ids);

    -- 第二个DELETE：同样复用预存的ID集合
    DELETE FROM comment 
    WHERE comment_id = ANY(target_ids);
END;
$$ LANGUAGE plpgsql;

方案二：用临时表存储结果 #

如果待处理的数据量较大，临时表的性能更优：

CREATE OR REPLACE PROCEDURE delete_comment(cid integer[]) AS $$
BEGIN
    -- 创建临时表存储待删除的ID
    CREATE TEMP TABLE temp_target_ids AS
    SELECT descendant AS id
    FROM comment_tree_path 
    WHERE ancestor = ANY(cid);

    -- 第一个DELETE：从临时表取ID
    DELETE FROM comment_tree_path
    WHERE descendant IN (SELECT id FROM temp_target_ids);

    -- 第二个DELETE：从临时表取ID
    DELETE FROM comment 
    WHERE comment_id IN (SELECT id FROM temp_target_ids);

    -- 临时表会在会话结束后自动销毁，也可手动删除
    DROP TABLE temp_target_ids;
END;
$$ LANGUAGE plpgsql;

2. 原代码第二个DELETE失效的原因 #

原代码中第一个DELETE直接删除了comment_tree_path表中符合条件的所有行，当执行第二个DELETE的子查询时，comment_tree_path已无对应数据，子查询返回空结果，因此无法删除comment表中的目标数据。通过预存查询结果，就能彻底规避这个问题。

内容的提问来源于stack exchange，提问作者Dante