错误俘获处理
teledb=# CREATE TABLE t_exception (id integer not null,nc text);
CREATE TABLE
teledb=# create unique index t_exception_id_uidx on t_exception using btree(id);
CREATE INDEX
teledb=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT AS
teledb-# $$
teledb$# BEGIN
teledb$# INSERT INTO t_exception VALUES(a_id,a_nc);
teledb$# RETURN '';
teledb$# EXCEPTION WHEN OTHERS THEN
teledb$# RETURN '执行出错';
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27(1,'teledb_pg');
f27
-----
(1 row)
teledb=# SELECT f27(1,'teledb_pg');
f27
----------
执行出错
(1 row)
获取错误相关信息
teledb=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT AS
teledb-# $$
teledb$# DECLARE
teledb$# v_sqlstate text;
teledb$# v_context text;
teledb$# v_message_text text;
teledb$# BEGIN
teledb$# INSERT INTO t_exception VALUES(a_id,a_nc);
teledb$# RETURN '';
teledb$# EXCEPTION WHEN OTHERS THEN
teledb$# GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
teledb$# v_message_text = MESSAGE_TEXT,
teledb$# v_context = PG_EXCEPTION_CONTEXT;
teledb$# RAISE NOTICE '错误代码 : %',v_sqlstate;
teledb$# RAISE NOTICE '出错信息 : %',v_message_text;
teledb$# RAISE NOTICE '发生异常语句 : %',v_context;
teledb$# RETURN '错误代码 : '||v_sqlstate || '\n出错信息 : '||v_message_text|| '发生异常语句 : '||v_context;
teledb$# END;
teledb$# $$
teledb-# LANGUAGE plpgsql;
CREATE FUNCTION
teledb=# SELECT f27(1,'teledb_pg');
NOTICE: 错误代码 : 23505
NOTICE: 出错信息 : node:16385, error 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 f27(integer,text) line 7 at SQL statement
f27
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
错误代码 : 23505\n出错信息 : node:16385, error duplicate key value violates unique constraint "t_exception_id_uidx"发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"+
PL/pgSQL function f27(integer,text) line 7 at SQL statement
(1 row)
