postgresql 在 PSQL 脚本中使用环境变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18725880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an environment variable in a PSQL script
提问by user1660256
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 * FROM a)
TO $outputdir/a.csv
Outputdir would be set in my environment. Is this possible?
Outputdir 将在我的环境中设置。这可能吗？
回答by MatheusOl
You can store the result of a shell command inside a psqlvariable 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 psqlvariables, see this answer of mine about psql variables, which is similar to your example. A example for your case would be:
另一个（在我看来更好）的解决方案是仅使用psql变量，请参阅我关于 psql variables 的这个答案，它与您的示例类似。你的情况的一个例子是：
\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
回答by benjwadams
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 questionfor a possibly more robust answer.
另请参阅此问题的已接受答案中的第二个片段，以获得可能更可靠的答案。
