| vishal saberwal 2006-01-26, 4:57 pm |
| 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_Han
dling_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_Han
dling_Function(varch
ar) 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;
|