sql - 从匿名 block 中的变量创建带密码的用户

Question

我想创建一个脚本，该脚本将包含 _user 和 _pass 变量，以在 Postgres 数据库中创建用户仅如果这样的登录尚不存在。我原以为这会起作用，但我不知道是什么问题:

DO
$DO$
DECLARE
  _user TEXT := 'myuser';
  _pass TEXT := 'user!pass';
BEGIN
   IF NOT EXISTS ( SELECT 1 FROM   pg_catalog.pg_roles WHERE  rolname = _user) THEN
        RAISE NOTICE 'Creating user % ...',_user;
        CREATE USER _user WITH
            LOGIN
            NOSUPERUSER
            CREATEDB
            CREATEROLE
            NOREPLICATION
            PASSWORD _pass;

        RAISE NOTICE 'Created user %',_user;
   ELSE
        RAISE NOTICE 'User % already exists, not creating it',_user;
   END IF;
END
$DO$

如何用变量的内容强制替换变量？

$DO$ 和 $$ 有什么区别？

Answer 1

要参数化标识符或语法元素，您通常需要使用带有 EXECUTE 的动态 SQL - 最好与 format() 结合使用以便于使用。

但实用命令(包括所有 SQL DDL 语句)根本不允许传递值或参数替换。您需要在执行之前连接完整的语句。见:

• “ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

你的代码会像这样工作:

DO
$do$
DECLARE
  _user text := 'myuser';
  _pass text := 'user!pass';
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _user) THEN
      EXECUTE format(
        'CREATE USER %I WITH
            LOGIN
            NOSUPERUSER
            CREATEDB
            CREATEROLE
            NOREPLICATION
            PASSWORD %L'
         , _user
         , _pass
         );
      RAISE NOTICE 'Created user "%"', _user;
   ELSE
      RAISE NOTICE 'User "%" already exists, not creating it', _user;
   END IF;
END
$do$

但是尽管 _user 和 _pass 无论如何都是硬编码的，但您可以像此处演示的那样进行简化:

• Create PostgreSQL ROLE (user) if it doesn't exist

Also what is the difference between $DO$ and $$?

参见:

• What are '$$' used for in PL/pgSQL