使用 PostgreSQL 和 bash 在单个事务中执行多个 .sql 文件 [英] Execute several .sql files in a single transaction using PostgreSQL and bash

问题描述

假设我有文件:

file1.sql
file2.sql
file3.sql

我需要在单个事务中执行所有三个文件.我正在寻找一个 bash 脚本，如:

I need all three files to be executed in a single transaction. I'm looking for a bash script like:

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 "".

我也尝试先连接到数据库，然后执行失败，如下所示:

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.

是否可以使用 PostgreSQL 和 bash 在单个事务中执行多个 .sql 文件?

Is it possible to execute several .sql files in a single transaction using PostgreSQL and bash?

每个文件的粗略结构是相似的:

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;

推荐答案

要么使用子 shell:

Either use a sub-shell:

#!/bin/sh
 (echo "BEGIN;"; cat file1.sql; cat file2.sql; echo "COMMIT;") 
 | psql -U the_user the_database

#eof

或使用此处的文档:

#!/bin/sh
psql -U the_user the_database <<OMG
BEGIN;

i file1.sql

i file2.sql

COMMIT;
OMG

#eof

注意:在 HERE 文档中将没有 globbing，因此 file*sql 将不被扩展.Shell 变量将被扩展，即使在引号内也是如此.

NOTE: in HERE-documents there will be no globbing, so file*sql will not be expanded. Shell-variables will be expanded, even within quotes.

这篇关于使用 PostgreSQL 和 bash 在单个事务中执行多个 .sql 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！