My goal is to create a common Exception handling stored function that takes Error Constant (as defined in Error Codes document: AppendixA) and raises a customized exception.
The problem is: (a) How do i catch these Error Constants? I was unable to use SQLSTATE and SQLERRM, for somehow the database didnt understand them. (b) How do i catch these from OTHERS exception and pass it to the Common Exception Handling function?
-- In SP fucntion, error could be in Inserts, divide by zero pr updates. -- The errors could be because a table is locked, or some other reasons. CREATE or replace SP(int) returns int as $$ DECLARE res int; BEGIN res:=0; insert into tbl values ('a','b','c'); res:=2/$1; update tbl set colA='x' where colA='a'; return res; EXCEPTION WHEN OTHERS THEN Common_Exception_Handling_Function(Error_Constant); END; $$ language plpgsql;
-- This common function will be called from EXCEPTION blocks of all Stored functions (around 300). -- All error codes will be defined in this common function and will raise a customized Exception message. CREATE or replace Common_Exception_Handling_Function(varchar) returns VOID as $$ BEGIN if $1='DIVISION_BY_ZERO' then RAISE EXCEPTION 'DIVISION BY ZERO'; elsif $1='SYNTAX_ERROR' then RAISE EXCEPTION 'SYNTAX ERROR'; . . . . . . . . . end if; END; $$ language plpgsql;