On Tue, 2 Oct 2018 at 16:48, David Gauthier <davegauthierpg@gmail.com> 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
translatedto something which someone could understand? Finally, how can I effectively do a start transaction and
eitherrollback 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?"
