Store multiline EOSQL and with \ char query in variable and run it with psql-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

To easily test your query, it's best to store it in a script and use -f from psql. But if you really need this query in the shell-script itself, you can use apostrophes to enclose your delimiter word (EOF) and inhibit shell expansion, than you can just copy-paste your tested sql-script into shell-script, like:

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

A better programming logic for your question was already pointed out by @Nic3500.