使用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;"

此操作失败，并显示错误:Syntax error at or near "\".

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;

推荐答案

要么使用子外壳，要么:

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

注意:在此处的文档中不会出现 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屋！