在PSQL脚本中使用环境变量 [英] Using an environment variable in a PSQL script
问题描述
可以在.sql文件中使用Linux环境变量吗?我正在使用复制/选择查询写入输出文件,我想把该目录放在变量中。所以我想做一些类似的东西:
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:
COPY (SELECT * FROM a)
TO $outputdir/a.csv
Outputdir将在我的环境中设置。这可能吗?
Outputdir would be set in my environment. Is this possible?
推荐答案
您可以将shell命令的结果存储在 psql
这样的变量:
You can store the result of a shell command inside a psql
variable like this:
\set afile `echo "$outputdir/a.csv"`
COPY (SELECT * FROM a) TO :'afile';
另一个(在我看来更好)解决方案是只使用 psql
变量,请参阅我的这个答案关于psql变量,这与你的例子相似。您的案例的一个例子是:
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:
\set outputdir '/path/to/output'
\set afile :outputdir '/a.csv'
COPY (SELECT * FROM a) TO :'afile';
请注意,在该示例中,您需要在脚本文件中设置变量,但您可以如果您调用 psql
时,请跳过第一行:
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 --set=outputdir="$outputdir" <conn parameters> -f /path/to/yourscript.sql
这篇关于在PSQL脚本中使用环境变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!