Re: many sql file and one transaction

 
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(at)gmail(dot)com>
To: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
Cc: salah jubeh <s_jubeh(at)yahoo(dot)com>; "andreas(at)a-kretschmer(dot)de" <andreas(at)a-kretschmer(dot)de>; pgsql <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, October 18, 2011 3:23 PM
Subject: Re: [GENERAL] many sql file and one transaction

On Tue, Oct 18, 2011 at 7:57 AM, Cédric Villemain
<cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
> 2011/10/18 salah jubeh <s_jubeh(at)yahoo(dot)com>:
>> 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(at)a-kretschmer(dot)de" <andreas(at)a-kretschmer(dot)de>
>> To: salah jubeh <s_jubeh(at)yahoo(dot)com>
>> Sent: Tuesday, October 18, 2011 2:23 PM
>> Subject: Re: [GENERAL] many sql file and one transaction
>>
>>
>> Zitat von salah jubeh <s_jubeh(at)yahoo(dot)com>:
>>
>>> 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