Creating user with password from variables in anonymous block

Question

I want to create a script that will have variables of _user and _pass to create the user in the Postgres database only if such login does not exist yet. I was thinking this would work, but i cant tell what is the issue:

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$

How do I enforce substitution of the variable with its content?

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

Answer 1

To parameterize identifiers or syntax elements, you generally need to use dynamic SQL with EXECUTE - best combined with format() for ease of use.

But utility commands (incl. all SQL DDL statements) do not allow passing of values or parameter substitution at all. You need to concatenate the complete statement before executing it. See:

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

Your code would work like this:

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$

But while _user and _pass are hardcoded anyway, you might simplify like demonstrated here:

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

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

See:

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