Issue

I have the following code in a shell script that initializes the postgres database in a docker container:

if [ "$ENV" == "development" ];
then
    psql --username "postgres" --dbname "postgres" <<EOSQL

    SELECT 'CREATE DATABASE $DATABASE' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DATABASE');\gexec

    \connect "$DATABASE";

    DO \$\$
    BEGIN
        -- Some stuff
    END
    \$\$;

    -- Other stuff
EOSQL
else
    psql --host "$HOST" --username "postgres" --dbname "postgres" <<EOSQL

    SELECT 'CREATE DATABASE $DATABASE' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DATABASE');\gexec

    \connect "$DATABASE";

    DO \$\$
    BEGIN
        -- Some stuff
    END
    \$\$;

    -- Other stuff
EOSQL
fi

In the if and else statement the SQL query is identical and I would like to put in a variable so I don't have to repeat it.

I tried to do QUERY="...", then psql ... -c "$QUERY" but I get errors on the \ char.

Is there a way to store this multiline SQL query in a variable and run it with psql

Solution

I always endeavor to avoid these cases and try to come up with a way around it if possible. You could do it this way (and not change anything in your query code which works already!):

hostoption=""
if [[ "$ENV" != "development" ]]
then
    hostoption="--host $HOST"
fi

psql $hostoption --username "postgres" --dbname "postgres" <<EOSQL

SELECT 'CREATE DATABASE $DATABASE' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DATABASE');\gexec

\connect "$DATABASE";

DO \$\$
BEGIN
    -- Some stuff
END
\$\$;

-- Other stuff
EOSQL

This way, hostoption is empty for development. And adding a space after psql will not break anything.

For other environments, it contains the host option.

Answered By - Nic3500

Answer Checked By - David Marino (WPSolving Volunteer)

Sunday, February 20, 2022

[SOLVED] Store multiline EOSQL and with \ char query in variable and run it with psql

Issue

Solution