如何在postgres中创建条件事务

Question

我正在尝试在事务中添加新角色并修改现有角色，如下所示：

BEGIN;
-- readonly role
CREATE ROLE readonly;
REVOKE CREATE ON SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON DATABASE some_database FROM readonly;
...
...
...
COMMIT;

但是，我只想在readonly角色不存在的情况下运行此事务。如何将此事务放入if condition？

Answer 1

您可以在plpgsql函数中执行此操作，该函数将自动在事务中运行。我认为，以下内容大致相当于你上面的样本：

DO $$
DECLARE
role_count int;
BEGIN
SELECT COUNT(*) INTO role_count FROM information_schema.enabled_roles
WHERE role_name = 'readonly';
IF role_count = 0 THEN
CREATE ROLE readonly;
REVOKE CREATE ON SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON DATABASE some_database FROM readonly;
...
...
...
ELSE
RAISE NOTICE 'readonly role already exists';
END IF;
END$$;

至于事务语义。。。虽然你已经将这个问题标记为第10页，但我们已经确认你正在运行第11页，所以你在这里有一些选择。

• 隔离运行上述操作，它将以原子方式运行，要么全部成功，要么不更改数据库
• 您可以将以上内容封装在BEGIN ... COMMIT/ROLLBACK中，并使其与上下文中的任何周围逻辑原子地运行
• 如果您转换为按照plpgsql事务使用过程，您还可以选择更多的granualr事务。考虑到你的问题，我认为这个时候你没有必要这么做

HTH