使用 PostgreSQL 和 bash 在单个事务中执行多个 .sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31537204/
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
Execute several .sql files in a single transaction using PostgreSQL and bash
提问by George Georgiev
Say I have the files:
说我有文件：
file1.sql
file2.sql
file3.sql
I need all three files to be executed in a single transaction. I'm looking for a bash script like:
我需要在单个事务中执行所有三个文件。我正在寻找一个 bash 脚本，如：
psql -h hostname -U username dbname -c "
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;"
This fails with an error: Syntax error at or near "\".
这失败并出现错误：Syntax error at or near "\"。
I also tried connecting to the DB first and then executing the fails, like that:
我还尝试先连接到数据库，然后执行失败，如下所示：
psql dbname
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;
This also fails, because the 'begin' command executes only when the connection is terminated.
这也会失败，因为“开始”命令仅在连接终止时执行。
Is it possible to execute several .sql files in a single transaction using PostgreSQL and bash?
是否可以使用 PostgreSQL 和 bash 在单个事务中执行多个 .sql 文件？
Edit:
编辑：
The rough structure of each of the files is similar:
每个文件的粗略结构都类似：
SET CLIENT_ENCODING TO 'WIN1251';
\i file4.sql
\i file5.sql
<etc>
RESET CLIENT_ENCODING;
回答by joop
Either use a sub-shell:
要么使用子外壳：
#!/bin/sh
(echo "BEGIN;"; cat file1.sql; cat file2.sql; echo "COMMIT;") \
| psql -U the_user the_database
#eof
or use a here-document:
或使用此处的文档：
#!/bin/sh
psql -U the_user the_database <<OMG
BEGIN;
\i file1.sql
\i file2.sql
COMMIT;
OMG
#eof
NOTE: in HERE-documents there will be no globbing, so file*sql will notbe expanded. Shell-variables will be expanded, even within quotes.
注意：在 HERE-documents 中不会有globbing，所以 file*sql不会被扩展。Shell 变量将被扩展，即使在引号内。
回答by TianyuZhu
You can also use the -1or --single-transactionoption to execute all your scripts in a transaction:
您还可以使用-1或--single-transaction选项在事务中执行所有脚本：
cat file*.sql | psql -1
回答by Gary
I'd create new files for the startup (start transaction, set encoding etc) and finish (commit).
我会为启动（开始事务、设置编码等）和完成（提交）创建新文件。
Then run something like:
然后运行类似：
cat startup.sql file*.sql finish.sql | psql dbname
回答by BingLi224
FYI, for Windows command line:
仅供参考，对于 Windows 命令行：
FOR /F "usebackq" %A IN (`dir *.sql /b/a-d`) DO psql -f %A
