错误俘获处理

teledb=# CREATE OR REPLACE PROCEDURE p_exception(a_id integer,a_nc text) AS
$$  
BEGIN       
    INSERT INTO t_exception VALUES(a_id,a_nc);
    RETURN ;
    EXCEPTION WHEN OTHERS THEN   
    RAISE NOTICE '执行出错';   
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=#

teledb=# CALL p_exception(1,'Teledb');
CALL
teledb=# CALL p_exception(1,'Teledb');
NOTICE:  执行出错
CALL

获取错误相关信息

teledb=# 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
teledb=# CALL p_exception_error(2,'Teledb');
CALL
teledb=# CALL p_exception_error(2,'Teledb');
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
teledb=#