Hi st 3. 10. 2018 v 18:26 odesÃlatel David Gauthier <davegauthie...@gmail.com> napsal:
> 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 > UPDATE xxxx IF NOT FOUND THEN RAISE EXCEPTION 'no rows updates'; END IF; or DECLARE rc int; BEGIN UPDATE xxxx GET DIAGNOSTICS rc = ROW_COUNT; IF rc = 0 THEN ... Regards Pavel > > 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 >>> >>
