13

Is it possible to create custom conditions when I raise an exception? Consider the following example:

BEGIN       
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

Here I use 'division_by_zero' condition to catch the exception. What I'd like to do is something like this:

BEGIN       
    [...]
    RAISE custom_condition;
EXCEPTION
    WHEN custom_condition THEN
       [...]
END;

so that I don't interfere with possible standard exceptions. I could just do y:= 1 / 0; and catch division_by_zero, but it does not look right.

Snifff
  • 1,784
  • 2
  • 16
  • 28

1 Answers1

33
begin
    if $1='bar' then
        raise exception using
            errcode='NOBAR',
            message='Bar is prohibited',
            hint='We do not talk to this guy';
    end if;
exception
    when sqlstate 'NOBAR' then
        update nobar_raised set count=count+1;
end;

More info:

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • 19
    Thanks! It works with one correction - the *errcode* parameter should be exactly five digits/uppercase ASCII characters, otherwise it will cause an error (`invalid SQLSTATE code`). Here is a note from your link: `Note: When specifying an error code by SQLSTATE code, you are not limited to the predefined error codes, but can select any error code consisting of five digits and/or upper-case ASCII letters, other than 00000. It is recommended that you avoid throwing error codes that end in three zeroes, because these are category codes and can only be trapped by trapping the whole category.` – Snifff Oct 14 '11 at 14:53
  • @Sniff: Thanks - I've corrected the case of errcode characters – Tometzky Oct 14 '11 at 16:06
  • @Tometzky: Which property under the exception.diagnostic does 'Bar is prohibited' show under? Is it MESSAGE_TEXT or PG_EXCEPTION_DETAIL or PG_EXCEPTION_HINT? (docs: http://goo.gl/F5w1y) – obimod Jul 13 '13 at 18:45