I'm trying to be as lazy as possible by generating a series of SQL commands in a file to feed to psql for processing. In a nutshell, I'm loading a series of import tables from outside sources (already done, via COPY), and then in a final step, deleting/updating/inserting records into the primary tables (which is functionally also done).
The only thing from preventing me from succeeding (and being able to do everything from a series of commands in a shell script) is the fact that sometimes the operation has referential integrity errors, and thus I have to roll back everything until the source can be identified and corrected.
So is there any way of knowing, from within a script processed by psql, if an error has occurred and to perform a rollback? And if there were no errors, commit.
I can always solve the problem by switching to a higher-level language, open a connection and run each statement and check for errors, but it's just all that more make-work code that I'd like to avoid if possible.