How can I give a named argument passed in psql?

Question

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.

Answer 1

Try:

 WHERE pg_stat_activity.datname = :'name' 

Pay attention to the placement of the colon before a separate quote. An excellent guide fills in the details here :

If an unquoted argument begins with a colon (:), it is taken as a psql variable, and the value of the variable is used as an argument instead. If the variable name is surrounded by single quotes (for example:: 'var'), it will be escaped as an SQL literal , and the result will be used as an argument. If the variable name is surrounded by double quotes, it will be escaped as an SQL identifier, and the result will be used as an argument.

My bold accent.