在PSQL脚本中使用环境变量

时间:2022-06-03 11:59:48

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.

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