错误俘获处理
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=#
