Postgres WITH 查询和多个后续语句

Question

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

WITH users_to_delete AS (
  SELECT id FROM auth.user WHERE email IN (
    '[e-mail]',
    '[e-mail]'
  )
)
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);

尝试执行此操作时，我在第二个语句上出现错误

关系"users_to_delete"不存在

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

Answer 1

使用多个 CTE：

WITH users_to_delete AS (
      SELECT id
      FROM auth.user
      WHERE email IN ('[e-mail]', '[e-mail]')
     ),
     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 完全是空的很奇怪。

Answer 2

使用帮助程序或临时表：

    begin;
    create temp  table users_to_delete () INHERITS  (auth.user);
    insert into users_to_delete  (SELECT id FROM auth.user WHERE email IN (
        '[e-mail]',
        '[e-mail]')     );
    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);
    ALTER TABLE users_to_delete  NO INHERIT auth.user;
    drop table users_to_delete;
    commit;