在PSQL中执行单个命令时使用变量

Question

当使用PSQL的变量时，我可以按以下方式运行它：

psql -d database -v var="'123'"

当我在PSQL终端输入以下内容时，我将能够访问变量var:

select * from table where column = :var;

当从文件中读取SQL语句时，这个可变特性也同样适用:

psql -d database -v var="'123'" -f file.sql

但是当我尝试将SQL作为单个命令运行时：

psql -d database -v var="'123'" -c "select * from table where column = :var;"

我无法访问该变量，出现以下错误：

ERROR:  syntax error at or near ":"

在PSQL中，是否可以将变量传递给单个SQL命令？

- Silveri

Answer 1

事实证明，就像man psql中解释的那样，-c命令只适用于 SQL，而其中 "不包含任何特定于 psql 的功能":

   -c command, --command=command
       Specifies that psql is to execute one command string, command, and then exit. This is useful in shell
       scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.

       command must be either a command string that is completely parsable by the server (i.e., it contains no
       psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands
       with this option. To achieve that, you could pipe the string into psql, for example: echo '\x \\ SELECT
       * FROM foo;' | psql. (\\ is the separator meta-command.)

看起来我可以通过使用stdin传递SQL来实现想要的功能：

echo "select * from table where column = :var;" | psql -d database -v var="'123'"

- Silveri