Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

st 3. 10. 2018 v 18:26 odesílatel David Gauthier <davegauthierpg@xxxxxxxxx> napsal:

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux