使用psql-cli中的\set变量在正常查询中工作，但在\copy中不工作/扩展

Question

如标题所述，问题是使用set设置的psql变量对我有效，除非在psql客户端提供的copy函数中使用

引用copy中的psql变量是否需要一些特殊语法？还是我运气不好？如果是的话，这有记录吗？

我在StackOverflow中找不到这个问题，也找不到任何地方的文档。我查看了大约20个帖子，但一无所获。我还查看了PostgreSQL 11(CLI版本)的\copy文档，没有发现任何关于这一点的警告——我在页面上搜索了"；变量"；没有发现任何与此相关的信息。我还搜索了"；扩展"；以及"；展开"；却一无所获。所以现在我在这里寻求帮助。。。

PostgreSQL客户端的版本是11.10，带有Debian应用的任何下游补丁：

psql (PostgreSQL) 11.10 (Debian 11.10-1.pgdg100+1)

我很确定服务器版本几乎没有相关性，但为了彻底起见，服务器是Ubuntu:提供的10.13版本

psql (PostgreSQL) 10.13 (Ubuntu 10.13-1.pgdg16.04+1) 

再现

我知道copy和COPY之间的区别(一个是作为psql客户端中的功能实现的，另一个是在服务器进程的上下文中执行的服务器功能)，对于这个任务，我需要使用的肯定是copy

显示我正在正确设置和引用变量的标准查询：

[local:/tmp]:5432 dbuser@dbdev# set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# set var_cname fname
[local:/tmp]:5432 dbuser@dbdev# SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1;
fname|lname|score|nonce
TestVal|C|100|b
(1 row)
Time: 88.786 ms

失败的案例，似乎是失败的，因为变量在copy内部被引用-我看不出这与工作示例之间有任何其他区别：

[local:/tmp]:5432 dbuser@dbdev# set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# set var_cname fname
[local:/tmp]:5432 dbuser@dbdev# copy (SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from :var_tname WHERE :var_cname = 'TestVal...
^
Time: 193.322 ms

显然，根据错误，扩展没有发生，查询试图引用一个字面名称为:var_tname的表

我没想到引用会有帮助，但试着以防万一——谁知道呢，这可能是一个奇怪的例外，对吧？不出所料，这也无济于事：

[local:/tmp]:5432 dbuser@dbdev# copy (SELECT * from :'var_tname' WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from : 'var_tname' WHERE :var_cname = 'Test...
^
Time: 152.407 ms
[local:/tmp]:5432 dbuser@dbdev# set var_tname 'ag_test'
[local:/tmp]:5432 dbuser@dbdev# copy (SELECT * from :var_tname WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from :var_tname WHERE :var_cname = 'TestVal...
^
Time: 153.001 ms
[local:/tmp]:5432 dbuser@dbdev# copy (SELECT * from :'var_tname' WHERE :var_cname = 'TestVal' LIMIT 1) TO 'testvar.csv';
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( SELECT * from : 'var_tname' WHERE :var_cname = 'Test...
^
Time: 153.459 ms

我还试着用单引号设置变量(这可能是最好的做法)，但这没有什么区别：

[local:/tmp]:5432 dbuser@dbdev# set var_tname 'ag_test'
[local:/tmp]:5432 dbuser@dbdev# set var_cname 'fname'
... <same behavior as above> ...

copy内部是否不支持变量扩展？如果是这样的话，这似乎是一个非常糟糕的限制，而且似乎没有记录

我希望有人会问，最后一件需要补充的事情是，我没有将这些功能或存储过程实现为。首先，我的PostgreSQL版本根本不支持存储过程。它也不支持函数中的事务。即使是这样，我希望在应用程序存储库中的psql文件中进行这些查询的真正原因是，它们很容易阅读以进行代码审查，很容易维护以进行开发，并且可以作为文档

没有必要阅读超过这一点，除非你也有这个问题，并且想要解决办法的想法

除此之外，我还记录了一系列我可以很快想到的解决方法——这个问题可以通过1001种不同的方式来解决。但是，如果有一个解决这种古怪行为的办法让我坚持下去，我宁愿知道它，也不愿采取任何变通办法。我还在下面添加了用例信息，因为它不是闻所未闻的；你为什么这么做？只是不要使用xyz功能，问题解决了&"；。我希望不会收到任何这样的回复：>

感谢任何愿意帮忙的人！

解决方案选项

我有很多解决方案，但我真的很想了解为什么这不起作用，如果它在某个地方被记录下来，或者如果在copy中使用时可能有一些特殊的方法导致扩展发生，以避免需要更改这一点-原因我在下面的用例部分中解释

以下是我想出的解决办法

使用变量选择到临时表中，\复制固定名称表

SELECT * INTO tmp_table FROM :var_tname WHERE :var_cname = 'TestVal' LIMIT 1;
copy (SELECT * FROM tmp_table) TO 'testvar.csv'

这是可行的，但它有点笨重，似乎不应该是不必要的

使用\pset fieldsetp生成TSV并将stdout重定向到文件(笨拙，可能存在转义问题)

另一个选项是不使用copy，并在将分隔符设置为tab后将stdout管道连接到文件：

[local:/tmp]:5432 dbuser@dbdev# set var_tname ag_test
[local:/tmp]:5432 dbuser@dbdev# pset format unaligned
Output format is unaligned.
[local:/tmp]:5432 dbuser@dbdev# pset fieldsep 't'
Field separator is "    ".
[local:/tmp]:5432 dbuser@dbdev# SELECT * from :var_tname LIMIT 1;
fname   lname   score   nonce
TestVal G   500 a
(1 row)
Time: 91.596 ms
[local:/tmp]:5432 dbuser@dbdev# 

这可以通过psql -f query.psql > /the/output/path.tsv调用。我还没有检查，但我假设应该生成一个有效的TSV文件。我不确定的一件事是，它是否会正确地转义或引用包含制表符的列值，如copy或COPY将

在shell脚本中进行扩展并写入临时psql文件，使用psql-f tmp.psql

最后的解决方法是在shell脚本中设置变量，并使用psql -c "$shellvar"调用，或者将shell扩展查询写入临时.psql文件，然后使用psql -f调用，并删除临时文件

用例(以及为什么我不特别喜欢一些解决方案)

我可能应该提到用例。。。我有几个独立的(但相关的)Python应用程序，它们收集、解析和处理数据，然后使用psycopg2将它们加载到数据库中。一旦原始数据在数据库中，为了可读性和减少需要维护的代码量，我将一堆较重的逻辑委托到psql文件中

psql文件是在应用程序完成时调用的，使用的方法如下：

for psql_file in glob.glob(os.path.expandvars("$VIRTUAL_ENV/etc/<appname>/psql-post.d/*.psql:
subprocess.call([which('psql'), '-f', psql_file])

我想在表名(和一些列名)中使用变量的原因之一是，数据库目前正在重构/重建中，因此表名和一些列名将随着时间的推移而重命名。因为有些.psql脚本非常广泛，表名在其中被引用了很多次，所以使用set在顶部设置一次表名更有意义，这样，当数据库中的每个表都发生更改时，每个psql文件只需要更改一次。未来可能还会有一些小的变化，使这种方法比需要搜索和替换各种列或表名的10-15个实例的方法更好

最后一个我不想使用的解决方法：从Python模板化psql文件

我意识到我可以直接从Python代码中使用一些自制的模板或Jinja2来从模板中动态生成PSQL文件。但我更喜欢在文件中使用纯psql，因为对于那些可能需要执行代码审查或在未来接管项目维护的人来说，它使项目更具可读性和可编辑性。它对我来说也更容易共事。显然，一旦我们开始讨论在Python中通过psycopg2使用查询来实现这一点，就有很多解决方案可供选择，但将.psql文件放在每个项目存储库的同一相对目录中会起到非常有用的作用

Answer 1

这似乎是copy的解析问题。UPDATE：实际上是一种记录在案的行为：https://www.postgresql.org/docs/current/app-psql.html

\copy。。。与大多数其他元命令不同，该行的整个剩余部分始终被视为\copy的参数，并且在参数中既不执行变量插值也不执行后引号展开。

提示

另一种获得与\copy相同结果的方法。。。to是使用SQL>复制。。。TO STDOUT命令，并用\g filename或\g>程序与\copy不同，此方法允许命令跨越多个>线此外，还可以使用变量插值和反引号展开。

set var_tname 'cell_per'
copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( select * from :var_tname ) TO STDOUT WITH (FORMAT CS...
copy (select * from :"var_tname") to stdout WITH (FORMAT CSV, HEADER);
ERROR:  syntax error at or near ":"
LINE 1: COPY  ( select * from : "var_tname" ) TO STDOUT WITH (FORMAT...
--Note the added space when using the suggested method of including a variable as 
--table name. 
copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
copy (select * from :"var_tname") to stdout WITH (FORMAT CSV, HEADER);
--Using COPY directly works.
--So:
o cp.csv
copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER);
o
--This opens file cp.csv  COPYs  to it and then closes file. 
-- Or per docs example and UPDATE:
copy (select * from :var_tname) to stdout WITH (FORMAT CSV, HEADER) g cp.csv

cat cp.csv  
line_id,category,cell_per,ts_insert,ts_update,user_insert,user_update,plant_type,season,short_category
5,H PREM 3.5,18,,06/02/2004 15:11:26,,postgres,herb,none,HP3
7,HERB G,1,,06/02/2004 15:11:26,,postgres,herb,none,HG
9,HERB TOP,1,,06/02/2004 15:11:26,,postgres,herb,none,HT
10,VEGGIES,1,,06/02/2004 15:11:26,,postgres,herb,annual,VG

Answer 2

我的解决方案是使用shell脚本将目标文件重命名为预定名称，然后启动引用该名称的psql脚本。

infile=$(find downloads -name '38*.CSV' | head -1)
rm -f importfile
ln -s $infile importfile
psql 'host=myHost ' -f copy_importfile.psql