Need help in taking care of the below requirement.

We need to take care of exceptions that could come in a pl sql block and log certain values from the select statement into a tailor made table - audit_log. For example:

audit_log table structure: col1, stored_procedure_name, error_code

CREATE OR REPLACE PROCEDURE SP_TEMP()
 LANGUAGE plpgsql
AS $procedure$
declare


begin
    /* loop through the data in table_a */ 
    for sq in (select a.column1,a.column2..a.columnN from table_a a  )

     
    loop
        /*Do some operations (not shown here) and select data from table_b */
        (                                                                                                                                                  
        select col1, col2, col3 
        from table_b b where 
        b.col1=sq.column1 )                                                                                                                                                  
        /*insert into table_c*/
        insert into table_c
        values(sq.column1,sq.column2,b.col2,b.col3);
        
    end loop;


   EXCEPTION:
   WHEN OTHERS THEN
    /* Log the failure information to audit_log table */
    insert into audit_log
    values(column1, 'SP_TEMP',SQLERRM)
    
    
end

$procedure$
;

Is it possible to do this? How to pass column1 value to the exception?

We were not able to pass the column1 value to the exception.

CodePudding user response：

Create a nested (inner block) inside the cursor loop. Then put your exception processing inside this block.

    create or replace procedure sp_temp()
     language plpgsql
    as $$
    declare
    begin
        /* loop through the data in table_a */ 
        for sq in (select a.column1,a.column2..a.columnn from table_a a  )
        loop
            
           begin  -- inner block to allow processing the exception
              /*do some operations (not shown here) and select data from table_b */
              (                                                                                                                                                  
              select col1, col2, col3 
              from table_b b where 
              b.col1=sq.column1 )                                                                                                                                                  
              /*insert into table_c*/
              insert into table_c
              values(sq.column1,sq.column2,b.col2,b.col3);
           exception
               when others then
                /* log the failure information to audit_log table */
                insert into audit_log
                values(sq.column1, 'sp_temp',sqlerrm);
    
            end; -- inner block   
        end loop;   
    end;
    $$;

NOTE: be careful of when others as the only predicate in the exception block. There are likely some conditions you want to handle and continue and others abort processing. Use when others only as the last resort.