主页 / dba / 问题 / 320088
Accepted
Dante
Asked: 2022-11-23 13:24:22 +0800 CST

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

  • 772

如何在以下函数中分解出常见的 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;
postgresql

2 个回答

  1. 您还可以使用 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) ;
    • 3
  2. Best Answer

    用于SELECT array_agg(...) INTO将目标列表拉入本地数组变量，然后将其作为参数传递给您DELETE的 s 使用= 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

相关问题