plpgsql trapping full detail error message

["Radovan Jablonov" <rjablonov@xxxxxxxx>]

Hello,

 

I would like to catch and then log full detailed error message from plpgsql function. I am using currently this method:

 

  WHEN OTHERS THEN
    v_actionstatus_id := 2;

 

    IF (v_log_flag = 1) --if 1 then log actions
    THEN
      INSERT INTO logdata.logaction(logaction_id, oper_id, action_by, actionstatus_id)
      VALUES(v_logaction_id, v_oper_id, p_action_by, v_actionstatus_id);
     
      INSERT INTO logdata.logactionparamint(logaction_id, operparam_id, operparam_value)
      VALUES(v_logaction_id, v_operparam_id1, p_session_id);
      INSERT INTO logdata.logactionparamint(logaction_id, operparam_id, operparam_value)
      VALUES(v_logaction_id, v_operparam_id2, p_action_by);  
      INSERT INTO logdata.logactionparamint(logaction_id, operparam_id, operparam_value)
      VALUES(v_logaction_id, v_operparam_id3, p_action_call_by);      
           
      INSERT INTO logdata.logerror(logaction_id, errno, error, descript)
      VALUES(v_logaction_id, SQLSTATE, SQLERRM, v_descript);
    END IF;     
 
    RETURN 'ERROR: '||SQLSTATE||' in stat.transegment('||p_session_id||','||p_action_by||','||p_action_call_by||'). Details in logdata.logaction WHERE logaction_id = '||v_logaction_id;

 

It works fine but it log only SQLERRM and not ,for example, error line and others details.

 

Sincerely,

Radovan Jablonovsky

Database Architect/DBA

Streamline Technology

Tel: (250) 571-7773

Email: rjablonov@xxxxxxxx