0

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)

Adam Birds
  • 394
  • 3
  • 21
  • 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

2 Answers2

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";
Zilog80
  • 2,534
  • 2
  • 15
  • 20
  • 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
0

It fails because u r using $$ in line 5 of your SQL script when you say DO $$BEGIN. Your command line cannot handle this, but you can do:

apt-get update && apt-get install -y gettext-base &&
psql -c "$(envsubst < /scripts/init-db.sql)"

This installs the envsubst package which not get irritated by $$. In my case I $DB_USER this annotation to declare variables in my SQL file instead of :"dbuser"

Camillo
  • 153
  • 1
  • 9