如何引用传入psql的命名参数? [英] How can I quote a named argument passed in to psql?

问题描述

psql 具有用于传递命名参数的结构:

psql has a construct for passing named arguments:

psql -v name='value'

然后可以在脚本中引用它:

which can then be referenced inside a script:

SELECT :name;

这将给出结果

 ?column?
----------
 value
(1 row)

在开发过程中，我需要相当频繁地删除并重新创建数据库副本，因此我试图使该过程自动化.因此，我需要运行一个查询，强制断开所有用户的连接，然后删除数据库.但是要对其进行操作的数据库会有所不同，因此数据库名称需要作为参数.

During development, I need to drop and recreate copies of the database fairly frequently, so I'm trying to automate the process. So I need to run a query that forcibly disconnects all users and then drops the database. But the database this operates on will vary, so the database name needs to be an argument.

问题是断开用户连接的查询需要一个字符串(WHERE pg_stat_activity.datname = 'dbname')，而删除用户的查询则需要一个未加引号的令牌(DROP DATABASE IF EXISTS dbname). (对不起.不知道该怎么称呼这种令牌.)

The problem is that the query to disconnect the users requires a string (WHERE pg_stat_activity.datname = 'dbname') and the query that drops requires an unquoted token (DROP DATABASE IF EXISTS dbname). (Sorry. Not sure what to call that kind of token.)

我可以在DROP查询中使用没有引号的命名实参，但是在断开连接查询中引用命名实参会导致该实参不被扩展.即，我将获得字符串':name'而不是字符串'value'.

I can use the named argument fine without quotes in the DROP query, but quoting the named argument in the disconnect query causes the argument to not be expanded. I.e., I would get the string ':name' instead of the string 'value'.

对于DROP查询，有没有办法将未引用的值转换为字符串或将字符串转换为未引用的令牌?我可以通过将断开连接和DROP查询放在单独的脚本中，并将带引号的参数传递给断开连接，而不带引号的参数传递给DROP来解决此问题，但是我宁愿它们位于同一脚本中，因为它们实际上是两个步骤在一个过程中.

Is there any way to turn the unquoted value into a string or turn a string into an unquoted token for the DROP query? I can work around it by putting the disconnect and DROP queries in separate scripts and passing the argument in with quotes to the disconnect and without quotes to the DROP, but I'd prefer they were in the same script since they're really two steps in a single process.

推荐答案

尝试:

WHERE pg_stat_activity.datname = :'name'

请注意在单引号之前的冒号位置.优秀的手册在此处的详细信息中进行了填写. :

Note the placement of the colon before the single quote. The excellent manual fills in on the details here:

如果未引用的参数以冒号(:)开头，则将其视为psql 变量，变量的值用作参数 反而.如果变量名称用单引号引起来(例如， :'var')，它将作为SQL文字转义，结果将是 用作参数.如果变量名被双精度包围 引号，它将被转义为SQL标识符，结果将是 用作参数.

If an unquoted argument begins with a colon (:), it is taken as a psql variable and the value of the variable is used as the argument instead. If the variable name is surrounded by single quotes (e.g. :'var'), it will be escaped as an SQL literal and the result will be used as the 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 the argument.

强调粗体.

这篇关于如何引用传入psql的命名参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！