sql - Postgres WITH 查询和多个后续语句

Question

我需要根据另一个表的内容从多个表中删除。为了避免在每个语句中都必须重新声明查询，我使用了 WITH:

WITH users_to_delete AS (
  SELECT id FROM auth.user WHERE email IN (
    'address@email.com',
    'address2@email.com'
  )
)

DELETE FROM schema1.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema2.table WHERE "userId" IN (SELECT id FROM users_to_delete);
DELETE FROM schema3.table WHERE "userId" IN (SELECT id FROM users_to_delete);

当尝试执行此操作时，我在

的第二条语句中遇到错误

relation "users_to_delete" does not exist

有没有办法将 users_to_delete 重复用于多个语句？

Answer 1

使用多个 CTE:

WITH users_to_delete AS (
      SELECT id
      FROM auth.user
      WHERE email IN ('address@email.com', 'address2@email.com')
     ),
     d1 AS (
      DELETE FROM schema1.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     ),
     d2 AS (
      DELETE FROM schema2.table
      WHERE "userId" IN (SELECT id FROM users_to_delete)
      RETURNING *
     )
DELETE FROM schema3.table
WHERE "userId" IN (SELECT id FROM users_to_delete);

不需要 returning 子句。我只是觉得 CTE 完全是空的很奇怪。