postgres psql error trying to pass parameters in sql script

Question

In postgresql, I'm psql with the -v for variable input that I can call within a sql file.

For example from bash script, it looks like this:

"$PSQL_HOME"/psql -h $HOST_NM     \
                      -p $PORT    \
                      -U postgres \
                      -v v1=$1    \
                      -f Test.sql
...
..

From the sql file, it looks like this:

GRANT ALL ON TABLE mytable TO mra_dev_:v1;
GRANT ALL ON TABLE mytable TO mra_dev_:v1_load;

The first statement works, but the 2nd statement fails:

psql:Test.sql:472: ERROR: syntax error at or near ":"
LINE 1: GRANT ALL ON TABLE mytable TO mra_dev_:v1_load
                                              ^

How do I get around this? Somekind of escape or concat feature I can use for this?

Answer 1

My workaround was to add the string I needed to the parameter when called on the command line like this:

"$PSQL_HOME"/psql -h $HOST_NM     \
                      -p $PORT    \
                      -U postgres \
                      -v v1=$1    \
                      -v v2=$_load \
                      -f Test.sql

Then within the sql file, changed this:

GRANT ALL ON TABLE mytable TO mra_dev_:v2;

It works now.