CODEWITHSUNDEEP
postgresqlexceptionplpgsql
Bart Friederichs
Bart Friederichs

Reputation: 33511

Raise and catch user defined exceptions

I use the RAISE EXCEPTION '...' USING ERRCODE='....' quite a lot in my code, as I can use the error code in my C# code. However, I would like to use it now in my plpgsql code, like this:

BEGIN
    ...
    RAISE EXCEPTION 'Something is wrong' USING ERRCODE='S0001';

EXCEPTION WHEN 'S0001' THEN
    -- Handle code S0001
END;

But that doesn't work. How can I catch and process my own thrown exceptions in plpgsql ?

Upvotes: 6

Views: 7034

Answers (2)

klin
klin

Reputation: 121604

Use sqlstate, e.g.:

drop function if exists test();
create or replace function test()
returns int language plpgsql as $$
begin
    raise exception using errcode = 50001;
    return 0;
    exception when sqlstate '50001' then
        return sqlstate;
end $$;

select test();

 test  
-------
 50001
(1 row)

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 246443

Your exception handling clause should look like this:

EXCEPTION
   WHEN SQLSTATE 'S0001'
   THEN
      ...
END;

Upvotes: 8

Related Questions