On Tue, 2 Oct 2018 at 16:48, David Gauthier <davegauthierpg@xxxxxxxxx> wrote: > > Hi: > psql (9.6.7, server 9.5.2) on linux > > How does one get the status of an sql statement executed in plpgsql? If that status is cryptic, how can that be translated to something which someone could understand? Finally, how can I effectively do a start transaction and either rollback or commit based on the results of the sql statements run? For the situations you describe, I suggest that it is simple enough to embrace the fact that stored functions run inside a pre-existing transaction context. You do not need to rollback or to return errors; you merely need to raise the exceptions. If *any* piece of the logic encountered an exception, then the transaction has fallen into an exception state, and will automatically be rolled back. You can "lean on this"; either: a) All of the logic passed with flying colours, and the transaction may happily proceed, or b) If any problem comes up along the way, the transaction is cancelled. In PostgreSQL 11 (not yet a production release), there now exist stored procedures that allow having BEGIN/COMMIT logic within a procedure: https://www.postgresql.org/docs/11/static/sql-createprocedure.html I would think it likely that you'd want to use a mixture of stored functions, that do some work on the assumption that it will all either succeed or fail, and then use a stored procedure to do transactional control on top of that. But as things stand today, the transaction control will need to take place in whatever layer you are using to control things. So, if you're using an app written in Python to control things, you'd submit the BEGIN/COMMIT on the Python side, and the stored functions run within transaction context. And next year, when PG11 is available, perhaps the BEGIN/COMMIT could reside in a stored procedure, so that the Python code gets a bit simpler. (Or s/Python/Java/g, or s/Python/PHP/g as needed...) -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
