psql has a construct for passing named arguments:
psql -v name='value'
which can then be referenced inside the script:
SELECT :name;
which will give a result
?column? ---------- value (1 row)
During development, I often need to drop and recreate copies of the database, so I'm trying to automate the process. Therefore, I need to run a query that forcibly disconnects all users and then deletes the database. But the database it runs on will change, so the database name must be an argument.
The problem is that the request to disconnect users requires a string ( WHERE pg_stat_activity.datname = 'dbname'
), and the request that needs to be refused requires an unquoted token ( DROP DATABASE IF EXISTS dbname
). (Sorry. Not sure what to name this token.)
I can use the named argument perfectly without quotes in the DROP request, but quoting the named argument in the disconnect request causes the argument not to expand. Ie, I would get the string ':name'
instead of the string 'value'
.
Is there a way to turn a value without quotes into a string, or turn a string into an unquoted token for a DROP request? I can get around this by putting the disconnect and DROP requests in separate scripts and passing the argument with the quotation marks to disconnect and without the quotes in DROP, but I would prefer that they be in the same script, since they are really two steps in one process.
source share