Commit and Exception Block

[<soumik.bhattacharjee@xxxxxxx>]

Hi Experts,

 

I have a procedure that may encounter an exception. I want to have an exception handling block where exception is to catch is any FK violations.

 

Could you please suggest where it’s getting missed as part of a transaction.

 

• PG Version : PostgreSQL 12.4

-          Initial Error - 2D000 cannot commit while a subtransaction is active

 

• Moved the exception block within another Begin/End block.
• But now à It does not go to the exception handling block itself.

 

 

Thanks in advance…

 

 

 

CREATE OR REPLACE PROCEDURE ddd.dddremove(

                p_number_of_rows integer,

                INOUT complete text)

LANGUAGE 'plpgsql'

 

AS $BODY$ DECLARE

tmprow ddd.order%rowtype;

p_counter     INTEGER := 0;

p_final_count INTEGER := 0;

cnt_result INTEGER :=0;

begin     

   FOR tmprow IN

      select idx from ddd.order where so_created_at< now() - interval '1460 days'

    LOOP               

      RAISE notice 'order Id %',tmprow.idx;

                  delete from ddd.order_settings where sos_order_id=100; 

                  delete from ddd.order where idx=tmprow.idx;

--               GET DIAGNOSTICS cnt_result = ROW_COUNT;

--               IF cnt_result = 0 THEN

--        RAISE NOTICE 'Removed % rows with amount = 0', cnt_result;

--        complete :='FOREIGN_KEY_VIOLATION';

--        return;

--      END IF;

                  p_counter := p_counter + 1;

     

      IF (p_counter !=0) then

       RAISE notice 'p_counter %',p_counter;

         COMMIT;

      END IF;

      EXIT WHEN p_counter > p_number_of_rows;

  

    END LOOP;

   begin

    RAISE SQLSTATE 'MYERR';

      EXCEPTION

      WHEN FOREIGN_KEY_VIOLATION then

      complete :='FOREIGN_KEY_VIOLATION';

      RETURN ;

      WHEN SQLSTATE 'MYERR' then

      complete :='Procedure Successful';

      RETURN ;

      WHEN no_data_found then

      complete :='FOREIGN_KEY_VIOLATION';

      RETURN ;

   end;

SELECT COUNT(*)

     INTO p_final_count

     FROM ddd.order where so_created_at< now() - interval '1460 days';

 

   RAISE NOTICE 'Sog Order Count After deleting % rows in the order table is %', p_counter,p_final_count;

    complete :='completed';

     return;

end $BODY$;