时间戳

首页 > 解决方案 > 多个 DELETE 语句的 Postgres 变量

postgresql - 多个 DELETE 语句的 Postgres 变量

问题描述

我们有一些没有级联设置的表，但我们想在删除项目时使用一个命令手动清理它们。

例如，要删除Permissions表和Votes基于项目的表identifier = 'abcdefg'，我们想先查找项目的id，然后用它来删除有问题的表：

WITH proj_id AS (
  SELECT id FROM "Projects" WHERE identifier = 'abcdefg'
)

DELETE FROM "Permissions" WHERE project_id IN (proj_id);
DELETE FROM "Votes" WHERE project_id IN (proj_id);
DELETE FROM "Gadgets" WHERE project_id IN (proj_id);
...
DELETE FROM "Projects" WHERE id IN (proj_id);

这不起作用，但我认为它说明了目标。

有没有一种方法可以在多个表中完成此 DELETE，而无需多次写入identifier值？

标签： postgresql

解决方案

将“项目”删除移动到 WITH 子句中，并让它返回 proj_id 列。然后用逗号分隔您的 DELETE 子句，使它们成为公用表表达式的一部分。

例如：

WITH proj_id AS (
  DELETE FROM "Projects"
  WHERE identifier = 'abcdefg'
  RETURNING id
),
perm_del AS (
  DELETE FROM "Permissions" p
  USING proj_id
  WHERE p.project_id = proj_id.id),
vote_del AS (
  DELETE FROM "Votes" v
  USING proj_id
  WHERE v.project_id = proj_id.id),
gadg_del AS (
  DELETE FROM "Gadgets" g
  USING proj_id
  WHERE g.project_id = proj_id.id),
...
SELECT id AS deleted
FROM proj_id;

推荐阅读