Thanks guys as you have pointed , I think the best solution is to go for CAT and set the appropriate options for psql.
Regards
From: Merlin Moncure <mmoncure@xxxxxxxxx>
To: Cédric Villemain <cedric.villemain.debian@xxxxxxxxx>
Cc: salah jubeh <s_jubeh@xxxxxxxxx>; "andreas@xxxxxxxxxxxxxxx" <andreas@xxxxxxxxxxxxxxx>; pgsql <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, October 18, 2011 3:23 PM
Subject: Re: many sql file and one transaction
On Tue, Oct 18, 2011 at 7:57 AM, Cédric Villemain
<cedric.villemain.debian@xxxxxxxxx> wrote:
> 2011/10/18 salah jubeh <s_jubeh@xxxxxxxxx>:
>> Hello,
>> Thanks for the reply.
>> I considered cat as an option but I did not go for it, because of the
>> number of sql files I have is large which makes the code not readable
>> The second thing, which is more important is because I have some advantages
>> with using -f such as the line number which causing the error.
>
> you can do :
>
> cat module1.sql \
> module2.sql \
> module_etc.sql \
> | psql -f -
>
>
>
>> Regards
>>
>>
>>
>>
>>
>> ________________________________
>> From: "andreas@xxxxxxxxxxxxxxx" <andreas@xxxxxxxxxxxxxxx>
>> To: salah jubeh <s_jubeh@xxxxxxxxx>
>> Sent: Tuesday, October 18, 2011 2:23 PM
>> Subject: Re: many sql file and one transaction
>>
>>
>> Zitat von salah jubeh <s_jubeh@xxxxxxxxx>:
>>
>>> Hello,
>>>
>>>
>>> I have many SQL script files to update schema, delete data, unit
>>> test ....etc. I want to run all the files in one transaction using
>>> shell script to ease the installation procedure. I can do that from
>>> the psql client by using the \i option
>>>
>>>
>>> BEGIN;
>>>
>>> \i / .../ module1.sql
>>>
>>> \i / .../ module2.sql
>>>
>>> \i / .../ module_etc.sql
>>> COMMIT;
>>>
>>>
>>> Is there a way to do that using psql command shell script.
>>>
>>>
>>> I.E.
>>>
>>> I want to run the following in one transaction,
>>>
>>> psql -f module1.sql
>>>
>>> psql -f module2.sql
>>>
>>> psql -f module_etc.sql
>>
>>
>> cat module1.sql module2.sql module_etc.sql | psql
also don't forget the -1 option to psql, which will wrap all your
commands in a transaction, and:
\set ON_ERROR_STOP
which will abort if there's an error. either put this in all your
scripts, your .psqlrc, or echo it into psql like this:
cat <(echo "\set ON_ERROR_STOP")
module1.sql \
module2.sql \
module_etc.sql \
| psql -1
you'll want to abort on error just to avoid the chance that a token in
your script will inadvertently close the transaction and cause a bunch
of garbage to be committed in the database -- this is likely to happen
say if you have a quotation error on function body.
merlin
