如何编写一个PostgreSQL脚本来在SQL9.1中创建角色,而不会在角色已经存在的情况下引发错误?
当前脚本只包含:
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
如果用户已经存在,则此操作将失败。我想要这样的东西:
IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user')
BEGIN
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
END;
..。但这并不起作用--在普通的SQL中似乎不支持IF
。
我有一个批处理文件,用于创建PostgreSQL 9.1数据库、角色和其他一些东西。它调用psql.exe,传入要运行的SQL脚本的名称。到目前为止,所有这些脚本都是纯SQL,如果可能的话,我想避免使用PL/pgSQL之类的脚本。
发布于 2012-12-14 00:09:14
或者,如果该角色不是可以使用的任何db对象的所有者:
DROP ROLE IF EXISTS my_user;
CREATE ROLE my_user LOGIN PASSWORD 'my_password';
但前提是删除此用户不会造成任何伤害。
发布于 2019-05-02 22:20:00
建议使用模式的一些答案:检查角色是否不存在,如果不存在,则发出CREATE ROLE
命令。这有一个缺点:竞争条件。如果其他人在检查和发出CREATE ROLE
命令之间创建了一个新角色,那么CREATE ROLE
显然会失败,并返回致命错误。
为了解决上面的问题,更多的其他答案已经提到了PL/pgSQL
的用法,无条件地发出CREATE ROLE
,然后从该调用中捕获异常。这些解决方案只有一个问题。它们静默地丢弃任何错误,包括那些不是由于角色已经存在而产生的错误。CREATE ROLE
还可以抛出其他错误,并且当角色已经存在时,模拟IF NOT EXISTS
应该只会静默错误。
角色已存在时发生CREATE ROLE
抛出duplicate_object
错误。异常处理程序应该只捕获这一个错误。正如其他答案所提到的,将致命错误转换为简单的通知是一个好主意。其他PostgreSQL IF NOT EXISTS
命令会将, skipping
添加到它们的消息中,所以为了保持一致性,我在这里也添加了它。
下面是用于模拟具有正确异常和sqlstate传播的CREATE ROLE IF NOT EXISTS
的完整SQL代码:
DO $$
BEGIN
CREATE ROLE test;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
测试输出(通过DO调用两次,然后直接调用):
$ sudo -u postgres psql
psql (9.6.12)
Type "help" for help.
postgres=# \set ON_ERROR_STOP on
postgres=# \set VERBOSITY verbose
postgres=#
postgres=# DO $$
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
DO
postgres=#
postgres=# DO $$
postgres$# BEGIN
postgres$# CREATE ROLE test;
postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
postgres$# END
postgres$# $$;
NOTICE: 42710: role "test" already exists, skipping
LOCATION: exec_stmt_raise, pl_exec.c:3165
DO
postgres=#
postgres=# CREATE ROLE test;
ERROR: 42710: role "test" already exists
LOCATION: CreateRole, user.c:337
发布于 2014-02-20 20:31:37
下面是一个使用plpgsql的通用解决方案:
CREATE OR REPLACE FUNCTION create_role_if_not_exists(rolename NAME) RETURNS TEXT AS
$$
BEGIN
IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN
EXECUTE format('CREATE ROLE %I', rolename);
RETURN 'CREATE ROLE';
ELSE
RETURN format('ROLE ''%I'' ALREADY EXISTS', rolename);
END IF;
END;
$$
LANGUAGE plpgsql;
用法:
posgres=# SELECT create_role_if_not_exists('ri');
create_role_if_not_exists
---------------------------
CREATE ROLE
(1 row)
posgres=# SELECT create_role_if_not_exists('ri');
create_role_if_not_exists
---------------------------
ROLE 'ri' ALREADY EXISTS
(1 row)
https://stackoverflow.com/questions/8092086
复制相似问题