Syntax error on $$ double dollars in Postgres function creation

Question

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?

ruakh · Accepted Answer · 2020-01-29T05:37:34.157

3

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.

edited Jan 29 '20 at 05:37

answered Jan 29 '20 at 05:24

ruakh

175,680
26
273
307

Thanks! That did the trick. Could you quote that paragraph explaining it? – MirroredFate Jan 29 '20 at 05:31
@MirroredFate: Sure -- done. – ruakh Jan 29 '20 at 06:54
Just a side note - do not add quotes to the closing mark, `EOSQL` in this case. – wpater Jan 24 '23 at 00:19

score 2 · Answer 2 · answered Jan 29 '20 at 05:26

$func and $$ are variables in bash. The first one is the process ID of the shell instance, which explains the number in your error output; the second is just a normal user-defined variable, presumably empty, so $test$ becomes just $ (as $test gets replaced by an empty string).

You can either escape the dollar sign that bash finds significant, using \$\$ and \$test\$, or you can use <<-'EOSQL' to use single-quote (no variable substitution) semantics on the heredoc.

None of this has to do with PostgreSQL, it's bash doing what it thinks you want.

Syntax error on $$ double dollars in Postgres function creation

2 Answers2