drop function if exists fw_core.fn_get_function_exception;
CREATE OR REPLACE FUNCTION fw_core.fn_get_function_exception(p_function_name text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$

 declare __client_id int ;
 declare __function_id int ;
 declare __file_mnemonic text  ;
 declare __file_sheet_mnemonic text ;
 declare __file_syspk int ;
 declare __fname text;
 declare err_state text;
 declare err_msg text;
 declare err_detail text;
 declare err_hint text;
 declare err_context text;
 declare function_query text;
 declare err_function text;
 declare v_function_parameter text;
 declare v_function_name text;
 declare v_function_name_len int;
 declare v_pos_ind int;

begin

function_query := 'select ' || p_function_name;
select length(p_function_name) into v_function_name_len;
select position('(' in p_function_name) into v_pos_ind;
select substring(p_function_name,0, v_pos_ind) into v_function_name;
select substring(p_function_name, v_pos_ind + 1, v_function_name_len - v_pos_ind - 1) into v_function_parameter;

select SPLIT_PART(v_function_parameter,',',1) into __client_id;
select SPLIT_PART(v_function_parameter,',',2) into __function_id;
select SPLIT_PART(v_function_parameter,',',3) into __file_mnemonic;
select SPLIT_PART(v_function_parameter,',',4) into __file_sheet_mnemonic;
select SPLIT_PART(v_function_parameter,',',5) into __file_syspk;
select trim('''' from __file_mnemonic) into __file_mnemonic;
select trim('''' from __file_sheet_mnemonic) into __file_sheet_mnemonic;

execute function_query;
/* staging2.fn_FTDRY_TRS_Block(20,1,'FTDRY','FTDRY_TRS',100) */

EXCEPTION when OTHERS then 

GET STACKED DIAGNOSTICS 
       err_state   = returned_sqlstate, /* P0002 */
       err_msg     = message_text,  /* No Data in FTDRY */
       err_detail  = pg_exception_detail, /* No data in table t_test_new */
       err_hint    = pg_exception_hint, /* No data error capture */
       err_context = pg_exception_context; /* PL/pgSQL function staging2.fn_ftdry_trs_block(integer,integer,text,text,integer) line 69 at RAISE
SQL statement "select staging2.fn_FTDRY_TRS_Block(20,1,'FTDRY','FTDRY_TRS',100)"
PL/pgSQL function trx.fn_get_function_exception(character varying) line 36 at EXECUTE */

if v_function_name like '%Block%' then
perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'stg2', v_function_name, err_state, err_msg, err_detail, err_hint, err_context,'error');
elsif v_function_name like '%ods%' then
perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'ods', v_function_name, err_state, err_msg, err_detail, err_hint, err_context,'error');
else 
perform  fw_core.fn_insert_db_error ( __client_id, __function_id, 1001, 'Compegence', __file_syspk ,__file_mnemonic,__file_sheet_mnemonic ,null,'trx', v_function_name, err_state, err_msg, err_detail, err_hint, err_context,'error');

end if;
END;
$function$