bashpostgresqlfunctionheredoc

Syntax error on $$ double dollars in Postgres function creation


I am attempting to create a simple function during the startup of a postgres instance. I am doing this by mapping some .sh files to the /docker-entrypoint-initdb.d dir.

I continuously hit a problem with this simple function.

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS $$
    BEGIN
        RETURN 'hi'
    END;
    $$;
EOSQL
2020-01-29 05:12:30.817 UTC [62] ERROR:  syntax error at or near "1" at character 49
2020-01-29 05:12:30.817 UTC [62] STATEMENT:  CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS 1
        BEGIN
            RETURN 'hi'
        END;
ERROR:  syntax error at or near "1"
LINE 1: CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS 1

If I change it to something with actual content:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS $func$
    BEGIN
        RETURN 'hi'
    END;
    $func$;
EOSQL

I get another error at the same place:

2020-01-29 05:17:36.161 UTC [62] ERROR:  syntax error at or near "$" at character 49
2020-01-29 05:17:36.161 UTC [62] STATEMENT:  CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS $
        BEGIN
            RETURN 'hi'
        END;
ERROR:  syntax error at or near "$"
LINE 1: CREATE OR REPLACE FUNCTION hi() RETURNS TEXT AS $

Running with the latest postgres version: 12.1

What precisely is the problem with this function definition and why am I getting this error?


Solution

  • The part of your script from <<-EOSQL to EOSQL is called a "here document", and that functionality is documented at the Bash Reference Manual, §3.6.6 "Here Documents". Per that section:

    If any part of word [in your case EOSQL] is quoted, […] the lines in the here-document are not expanded. If word is unquoted, all lines of the here-document are subjected to parameter expansion, command substitution, and arithmetic expansion, […]

    In other words — because you haven't quoted any part of EOSQL, Bash is performing parameter expansion (and other similar substitutions) on the contents of the here-document, which includes replacing $$ with the process-ID, and $func with the empty string, before PostgreSQL sees them.

    If you just change <<-EOSQL to <<-'EOSQL', it won't do that.