Thread: Catching errors in pl/pgsql

Hi everybody!

I'm migrating severals application from Oracle to PostgreSQL. I read all
the documents about PL/PGSQL and how porting from oracle to pg. I ported
several procedures succesful, but I have a lot of problems "translating"
this kind of code:
(...)
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
         V_Error := 'PR_INSERT_VIOLATION_PK';
      WHEN E_Parent_Not_Found THEN
         V_Error := 'PR_INSERT_PARENT_NOT_FOUND';
      WHEN OTHERS THEN
         V_Error := 'PR_INSERT_OTHERS';
(...)

   IF vError IS NULL THEN
      COMMIT;
   ELSE
      ROLLBACK;
      RAISE_APPLICATION_ERROR(-20000,V_Error);
   ENDIF;
(...)

How I can catch the error, for example, if I try to insert a duplicate
value on primary key? or if parent keys not found? I need to catch the
error inside the pl/pgsql function and then pass a message to
application. I will rise a exception (I know how to do this) but I don't
know how to identify the errors...

Thanks in advance!

--
Fernando O. Papa
DBA

On Friday 14 Mar 2003 2:20 pm, Fernando Papa wrote:
> Hi everybody!
>
> I'm migrating severals application from Oracle to PostgreSQL. I read all
> the documents about PL/PGSQL and how porting from oracle to pg. I ported
> several procedures succesful, but I have a lot of problems "translating"
> this kind of code:
> (...)
>    EXCEPTION
>       WHEN DUP_VAL_ON_INDEX THEN
>          V_Error := 'PR_INSERT_VIOLATION_PK';

> How I can catch the error, for example, if I try to insert a duplicate
> value on primary key? or if parent keys not found? I need to catch the
> error inside the pl/pgsql function and then pass a message to
> application. I will rise a exception (I know how to do this) but I don't
> know how to identify the errors...

You can't at the moment. There's no way to catch an error inside a function -
an error aborts the current transaction without you being able to catch it.

I'm afraid you're going to have to check for whatever causes the error and
avoid it instead.

--
  Richard Huxton