7

I need to be able to pass environment variables, from a bash shell executing a .sql file using psql.

The psql command I am running is:

su postgres -c "psql -v ON_ERROR_STOP=1 -v dbname=example -v dbuser=example -e" < ./create-db.sql

Where I have put example is usually set to $DATABASE_NAME and DATABASE_USER respectively which is set in the bash script before executing the psql command.

The create-db.sql file is:

\connect postgres
DROP DATABASE IF EXISTS :dbname;
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ALTER ROLE :"dbuser" SET search_path TO :"dbname",public;
CREATE DATABASE :"dbname"
    OWNER=:dbuser
    ENCODING=UTF8
    LC_COLLATE='en_US.UTF-8'
    LC_CTYPE='en_US.UTF-8'
    TEMPLATE=template0;
\connect :"dbname"
CREATE SCHEMA zulip AUTHORIZATION :"dbuser";

The variable substitution in the line DROP DATABASE IF EXISTS :dbname; works, but everything after fails at the CREATE USER :dbuser; line fails and I have tried it as each of the following ways:

CREATE USER :dbuser; CREATE USER :'dbuser'; CREATE USER :"dbuser";

And it always errors with:

You are now connected to database "postgres" as user "postgres".
DROP DATABASE IF EXISTS example;
NOTICE:  database "example" does not exist, skipping
DROP DATABASE
DO $$BEGIN
    CREATE USER :dbuser;
EXCEPTION WHEN duplicate_object THEN
    RAISE NOTICE 'user already exists';
END$$;
ERROR:  syntax error at or near ":"
LINE 2:     CREATE USER :dbuser;
                        ^

I'm really not sure where I'm going wrong with the above. Output of psql -V is psql (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)

1
  • Anonymous code blocks don't accept variables. Perhaps you could use a temporary function? Mar 29, 2021 at 11:09

1 Answer 1

2

This will allow you to do it. In your case, everything between '$$' is a "dollar-quoted string constant, so nothing gets interpreted. See sql syntax, 4.1.2.4.

\set CREDO 'begin create user ' :dbuser '; exception when duplicate_object then raise notice ''user already exists''; end';
do :'CREDO';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.