postgresql - postgres psql error trying to pass parameters in sql script -


in postgresql, i'm psql -v variable input can call within sql file.

for example bash script, looks this:

"$psql_home"/psql -h $host_nm     \                       -p $port    \                       -u postgres \                       -v v1=$1    \                       -f test.sql ... .. 

from sql file, looks this:

grant on table mytable mra_dev_:v1; grant on table mytable mra_dev_:v1_load; 

the first statement works, 2nd statement fails:

psql:test.sql:472: error: syntax error @ or near ":" line 1: grant on table mytable mra_dev_:v1_load                                               ^ 

how around this? somekind of escape or concat feature can use this?

my workaround add string needed parameter when called on command line this:

"$psql_home"/psql -h $host_nm     \                       -p $port    \                       -u postgres \                       -v v1=$1    \                       -v v2=$_load \                       -f test.sql 

then within sql file, changed this:

grant on table mytable mra_dev_:v2; 

it works now.


Comments