获取错误相关信息

• 复制
  复制成功

postgres=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT AS
postgres-# $$    
postgres$# DECLARE    
postgres$#     v_sqlstate text;
postgres$#     v_context text;
postgres$#     v_message_text text;
postgres$# BEGIN           
postgres$#     INSERT INTO t_exception VALUES(a_id,a_nc);
postgres$#     RETURN '';
postgres$#     EXCEPTION WHEN OTHERS THEN 
postgres$#     GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
postgres$#                                  v_message_text = MESSAGE_TEXT,
postgres$#                                  v_context = PG_EXCEPTION_CONTEXT;     
postgres$#     RAISE NOTICE '错误代码 : %',v_sqlstate;
postgres$#     RAISE NOTICE '出错信息 : %',v_message_text;
postgres$#     RAISE NOTICE '发生异常语句 : %',v_context; 
postgres$#     RETURN '错误代码 : '||v_sqlstate || '\n出错信息 : '||v_message_text|| '发生异常语句 : '||v_context;   
postgres$# END;
postgres$# $$
postgres-# LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT f27(1,'TBase');
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)