I found "no_data" here... https://www.postgresql.org/docs/10/static/errcodes-appendix.html
update blah, blah... if(no_data) then raise exception "update failed to update anything"; end if Is that how you detect if nothing was updated and how to make sure the thing returns and rolls back ? On Wed, Oct 3, 2018 at 11:46 AM David Gauthier <davegauthie...@gmail.com> wrote: > Thanks Adrian and Christopher ! > > So the transaction will be rolled back automatically if there's a > problem. Got it ! > > Question: How do I detect when it is appropriate to raise notice so as to > be able to see the error message? Or will that automatically be sent to > stdout if an error occurs? > > Question: Also, how can I detect how many records were operated on by, > say, an update statement? In the example I gave, I would want to make sure > that the transfer amount was deducted from the savings AND that the amount > was added to the checking. Both need to be 1, else I want to... > 1) send a message (raise notice) > 2) rollback somehow. > > > > > On Tue, Oct 2, 2018 at 5:27 PM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 10/2/18 1:47 PM, David Gauthier 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? >> > >> >> > >> > Of course I don't know what the <capture the status> and >> > "something_went_wrong" pieces look like, or they even make sense with >> > how this sort of thing shold be properly handled in plpgsql. Also, in >> >> The below(read to bottom of the page) might help: >> >> >> https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> >> >> > my trials, it appears that plpgsql doesn't like "start transaction". >> So >> > how is that piece done ? >> > >> > Thanks in Advance for any help ! >> > >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >
