Postgres WITH query and multiple subsequent statements

Question

I need to delete from multiple tables, according to the contents of another table. To avoid having to re-declare a query in every statement, I'm using WITH:

WITH users_to_delete AS (
  SELECT id FROM auth.user WHERE email IN (
    '[email protected]',
    '[email protected]'
  )
)

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);

When trying to execute this, I get an error on the second statement of

relation "users_to_delete" does not exist

Is there a way to re-use the users_to_delete for multiple statements?

Answer 1

Use multiple CTEs:

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

The returning clauses are not needed. I just find it strange to have CTEs that are entirely empty.