获取错误相关信息

• 复制
  复制成功

postgres=# CREATE OR REPLACE PROCEDURE p_exception_error(a_id integer,a_nc text) AS
$$    
DECLARE    
    v_sqlstate text;
    v_context text;
    v_message_text text;
BEGIN           
    INSERT INTO t_exception VALUES(a_id,a_nc);
    RETURN ;
    EXCEPTION WHEN OTHERS THEN 
    GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
                                 v_message_text = MESSAGE_TEXT,
                                 v_context = PG_EXCEPTION_CONTEXT;     
    RAISE NOTICE '错误代码 : %',v_sqlstate;
    RAISE NOTICE '出错信息 : %',v_message_text;
    RAISE NOTICE '发生异常语句 : %',v_context; 
    raise notice '错误代码 : % \n出错信息 : % 发生异常语句 : %',v_sqlstate ,v_message_text,v_context;   
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_exception_error(2,'Tbase');
CALL
postgres=# CALL p_exception_error(2,'Tbase');
NOTICE:  错误代码 : 23505
NOTICE:  出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx"
NOTICE:  发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
NOTICE:  错误代码 : 23505 \n出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx" 发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
CALL
postgres=#