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 <davegauthierpg@xxxxxxxxx> 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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
