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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

st 3. 10. 2018 v 18:26 odesílatel David Gauthier <davegauthierpg@xxxxxxxxx> 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@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