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

Hi

st 3. 10. 2018 v 18:26 odesílatel David Gauthier <davegauthierpg(at)gmail(dot)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 <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
>>>
>>