hi all,
I am using PostgreSQL 8.0.1 on Fedora core 2.
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;
