在PSQL脚本中使用环境变量

Is it possible to use a Linux environment variable inside a .sql file? I'm using the copy/select query to write to an output file, and I'll like to put that directory in a variable. So I want to do something like:

是否可以在.sql文件中使用Linux环境变量？我正在使用copy / select查询写入输出文件，我想将该目录放在变量中。所以我想做的事情如下：

COPY (SELECT * FROM a)
TO $outputdir/a.csv

Outputdir would be set in my environment. Is this possible?

Outputdir将在我的环境中设置。这可能吗？

2 个解决方案

#1

You can store the result of a shell command inside a psql variable like this:

您可以将shell命令的结果存储在psql变量中，如下所示：

\set afile `echo "$outputdir/a.csv"`
COPY (SELECT * FROM a) TO :'afile';

Another (better in my opinion) solution is to use only psql variables, see this answer of mine about psql variables, which is similar to your example. A example for your case would be:

另一个（在我看来更好）解决方案是只使用psql变量，看看我的关于psql变量的答案，这与你的例子类似。您案例的一个例子是：

\set outputdir '/path/to/output'
\set afile :outputdir '/a.csv'
COPY (SELECT * FROM a) TO :'afile';

Note that, in the example, you need to set the variable inside the script file, but you can skip the first line if you set it when you call psql:

请注意，在示例中，您需要在脚本文件中设置变量，但是如果在调用psql时设置它，则可以跳过第一行：

psql --set=outputdir="$outputdir" <conn parameters> -f /path/to/yourscript.sql

#2

-1

This appears to work for your use case, provided you single quote the output file name as I mentioned. It will escape any double quotes as well contained within the SQL.

这似乎适用于您的用例，前提是您按照我提到的单引号输出文件名。它将转义SQL中包含的任何双引号。

psql -c "$(eval echo '"' $(<envvars.sql | sed 's/"/\\"/g') '"')"

Of course, note that if your file contains any dollar quoted variables, the shell is going to try to interpret as a variable, and your script will break, so you will need to escape any dollar signs you need preserved literally with a backslash.

当然，请注意，如果您的文件包含任何美元引用变量，那么shell将尝试将其解释为变量，并且您的脚本将会中断，因此您需要使用反斜杠转义所需的任何美元符号。

See also the second snippet in the accepted answer to this question for a possibly more robust answer.

另请参阅此问题的已接受答案中的第二个片段，以获得更可靠的答案。

#1