CODEWITHSUNDEEP
postgresqlstored-procedures
Raghav
Raghav

Reputation: 19

Postgres Exception handling in stored procedure to continue the process

Database is postgressql.

I want to call five stored procedure in on stored procedure that is master_call_all() and need to handle exception after every call. So if any error occur it raise the exception and not terminate the process. Let say error occur in 3rd call scratch.sp_insert3() then after raising error next stored procedure will call. This thing I want to achieve after every call.

create or replace procedure scratch.master_call_all()
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
    call scratch.sp_insert1();
    call scratch.sp_insert2();
    call scratch.sp_insert3();
    call scratch.sp_insert4();
    call scratch.sp_insert5();
commit; 
END;
$$;

Upvotes: 0

Views: 10044

Answers (1)

Belayer
Belayer

Reputation: 14936

First off: In general this is an extremely bad plan. It is the anti-thesis of creating a Transaction (begin ... end).

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Ref: Database Transactions

Now with that out of the way, what you are asking can be done, but it is not done cleanly. A code block in Postgres contains 3 sections declaration, execution, exception with declaration and exception optional. Further a block can be nested inside another and the nested block retains all 3 sections. So what you need is to place each ca;; statement into a nested block. Like:

create or replace procedure master_call_all()
language plpgsql
as $$
declare
begin
    begin 
        call  sp_insert1();
    exception 
        when others then 
             raise notice 'Exception occurred calling sp_insert1()';
    end ; 
        
        begin 
            call  sp_insert2();
        exception 
            when others then 
                 raise notice 'Exception occurred calling sp_insert2()';
        end ;
     
        begin 
            call  sp_insert3();
        exception 
            when others then 
                 raise notice 'Exception occurred calling sp_insert3()';
        end ;
     
        begin 
            call  sp_insert4();
        exception 
            when others then 
                 raise notice 'Exception occured calling sp_insert4()';
        end ; 
        
        begin 
            call  sp_insert5();
        exception 
            when others then 
                 raise notice 'Exception occured calling sp_insert5()';
        end ;
    
    commit; 
    end;
    $$;

See demo here. Unfortunately db<>fiddle does not show the messages from raise notice .... Clearly in the demo sp_insert1, and sp_insert3 will raise an exception. (To see the messages run in your environment.)

Upvotes: 3

Related Questions