Re: How can I get and handle the status of sql statements that run in plpgsql ?

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(at)gmail(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>
>