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?
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.