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
31
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
31
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.
另请参阅此问题的已接受答案中的第二个片段,以获得更可靠的答案。