Execute multiple .sql files in one transaction using PostgreSQL and bash

Question

Say I have files:

file1.sql
file2.sql
file3.sql

I need all three files to be executed in one transaction. I am looking for a bash script like:

psql -h hostname -U username dbname -c "
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;"

Error with the error Syntax error at or near "\".

I also tried connecting to the database first and then crashing, for example:

psql dbname
begin;
\i file1.sql
\i file2.sql
\i file3.sql
commit;

This also fails because the begin command is only executed when the connection is complete.

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

Edit:

The rough structure of each file is similar:

SET CLIENT_ENCODING TO 'WIN1251';
\i file4.sql
\i file5.sql
<etc>
RESET CLIENT_ENCODING;

Answer 1

Or 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 here-document:

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

\i file1.sql

\i file2.sql

COMMIT;
OMG

#eof

. , * sql . Shell- , .

Answer 2

-1 --single-transaction :

cat file*.sql | psql -1

Answer 3

I would create new files to start (start a transaction, set the encoding, etc.) and complete (commit).

Then run something like:

cat startup.sql file*.sql finish.sql | psql dbname

Answer 4

For your information, for the Windows command line:

FOR /F "usebackq" %A IN ('dir *.sql /b/a-d') DO psql -f %A