Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Comments on Postgres command-line variable substitution error
Parent
Postgres command-line variable substitution error
According to the psql (version 14) man page, it's possible to set variables using the -v command-line parameter, then use the variable in a query, automatically quoted correctly, with :'variable_name'. When issuing SQL commands with -c, this works:
$ psql -v foo=bar -c "\echo :foo"
bar
...and this works:
$ psql -v foo=bar -c "\echo :'foo'"
'bar'
...but this fails:
$ psql -v foo=bar -c "SELECT :'foo' AS foo"
ERROR: syntax error at or near ":"
LINE 1: SELECT :'foo' AS foo
^
Why doesn't the latter syntax work? How can I get a variable substitution via SQL interpolation from the command line?
Post
I don't know the "real" answer, but from bash on Linux at least, it's possible to work around the issue by using -f instead of -c, using process substitution to supply the SQL:
$ psql -v foo=bar -f <(echo "SELECT :'foo' AS foo;")
foo
-----
bar
(1 row)
1 comment thread
0 comment threads