How to pass environment variables to a sql file from bash shell (PostgreSQL)

Question

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)

Read this section [Dynamic Commands](https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). `CREATE USER` is a utility command and the complete command needs to be built textually. — Adrian Klaver, Mar 28 '21 at 17:11
@AdrianKlaver I have looked at that document, but still can't seem to get it to work. — Adam Birds, Mar 28 '21 at 17:27

Zilog80 · Answer 1 · 2021-03-28T22:13:07.307

0

Without PLSQL, it seems to work :

\connect postgres
DROP DATABASE IF EXISTS :dbname;
CREATE USER :dbuser;
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";

edited Mar 28 '21 at 22:13

answered Mar 28 '21 at 17:39

Zilog80

2,487
2
14
19

Unfortunately both didn't work, they still error about syntax issue at : – Adam Birds Mar 28 '21 at 18:30
Yes the short version is that `psql` variables will not be expanded in `plpgsql` functions per this post [Variables](https://www.postgresql.org/message-id/CAFj8pRAOU4pvGLhzeD%2BHBQ5a-c0onzPHL-gebOhieiy%3DjETUUQ%40mail.gmail.com) which points at this SO [Post](https://stackoverflow.com/questions/13172524/passing-user-id-to-postgresql-triggers/13172964#13172964) for possible work arounds. – Adrian Klaver Mar 28 '21 at 20:07
@AdrianKlaver Passing parameters to an anonymous plsql bloc seems [not possible](https://stackoverflow.com/questions/34912786/reference-psql-parameter-inside-pl-pgsql-anonymous-block). I'll review the answer with a function... – Zilog80 Mar 28 '21 at 22:17
Passing in parameters and using set variables are two different things. An anonymous function of any language cannot take parameters. Set variables cannot be used in a function, anonymous or not. – Adrian Klaver Mar 29 '21 at 16:35

How to pass environment variables to a sql file from bash shell (PostgreSQL)

1 Answers1