4

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 $$?

Improve this question
2
  • 2
    You need dynamic SQL for that Commented Mar 8, 2018 at 9:29
  • 2
    There is no difference between $DO$ or $$ or $lkdazlkfdj$, as long as you have a matching delimiter at the other end of the block: postgresql.org/docs/current/static/… Commented Mar 8, 2018 at 13:30

2 Answers 2

Reset to default
5

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:

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:

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

See:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Another way of ab(using) customized options ( quoting Erwin's older answers) :

aka in bash: 

 set -x PGPASSWORD="${postgres_db_useradmin_pw:-}" psql -q -t -X \ -w -U "${postgres_db_useradmin:-}" \ -h $postgres_db_host -p $postgres_db_port \ -v ON_ERROR_STOP=1 \ -v postgres_db_user="${postgres_db_user:-}" \ -v postgres_db_user_pw="${postgres_db_user_pw:-}" \ -v postgres_db_name="${postgres_db_name:-}" \ -f "$sql_script" "${postgres_db_name:-}" > "$tmp_log_file" 2>&1

and in pgsql

 SET myvars.postgres_db_user TO :'postgres_db_user'; SET myvars.postgres_db_user_pw TO :'postgres_db_user_pw'; DO $do$ BEGIN EXECUTE format( 'CREATE ROLE %I WITH PASSWORD %L LOGIN' , current_setting('myvars.postgres_db_user', true)::text , current_setting('myvars.postgres_db_user_pw', true)::text ); RAISE NOTICE 'Created user "%"', current_setting('myvars.postgres_db_user', true)::text; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'User "%" already exists, not creating it', current_setting('myvars.postgres_db_user', true)::text; EXECUTE format( 'ALTER ROLE %I WITH PASSWORD %L LOGIN' , current_setting('myvars.postgres_db_user', true)::text , current_setting('myvars.postgres_db_user_pw', true)::text ); END $do$;

worth noting that often the same result might be achieved much more easier by means of a combination of bash vars interpolation and sql like this: https://github.com/YordanGeorgiev/qto/blob/master/src/bash/qto/funcs/provision-db-admin.func.sh

Improve this answer

1 Comment

The link provided is no longer available. @Yordan , could you please update it? It would be very useful to the community.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.