Thread: Exception handling

Can someone explain me about the exception handling in postgresql. Iam
not understanding the scope of a exception block. The exact confusion
that iam facing is as follows:
I have a procedure as follows
BEGIN
EXECUTE an update statement

EXECUTE an insert statement
EXCEPTION WHEN integrity_conatraint_violation THEN
RAISE NOTICE
END;

This procedure is called within another procedure. Now when an
exception is caught the updates are also getting rolled back. I need
the exception block to work only for the insert statement. But if i
dont write the exception block the whole program stops.
Any suggestions?

Thanks
Sam

On Wed, 4 Jun 2008, sam wrote:

> Can someone explain me about the exception handling in postgresql. Iam
> not understanding the scope of a exception block. The exact confusion
> that iam facing is as follows:
> I have a procedure as follows
> BEGIN
> EXECUTE an update statement
>
> EXECUTE an insert statement
> EXCEPTION WHEN integrity_conatraint_violation THEN
> RAISE NOTICE
> END;
>
> This procedure is called within another procedure. Now when an
> exception is caught the updates are also getting rolled back. I need
> the exception block to work only for the insert statement. But if i
> dont write the exception block the whole program stops.
> Any suggestions?

I think you're looking for something like:

BEGIN
 EXECUTE an update
 BEGIN
  EXECUTE an insert
  EXCEPTION WHEN ...
   RAISE NOTICE ...
 END;
END;

The exception when is effectively associated with the block.