How to print code, type of PostgreSQL / plpgsql exception

Question

Given this pl/pgSQL function

drop function if exists f( float );
create function f( x float )
  returns float
  language plpgsql
  as $$
    begin
      return 1 / x;
    exception
      when others then
        raise notice 'oops';
        return 0::float;
    end;
  $$;

it is clear that select f( 0 ); will result in an code 22012 exception, type division_by_zero. Knowing this I can narrow down the selector of the exception clause to when division_by_zero then ....

However, for arbitrary functions, how can I obtain the error type? Is there anything like, say, raise notice error.code?

klin · Accepted Answer · 2017-06-17 15:58:51Z

5

Use sqlstate, example:

drop function if exists f( float );
create function f( x float )
  returns float
  language plpgsql
  as $$
    begin
      return 1 / x;
    exception
      when others then
        raise notice 'oops %', sqlstate;
        return 0::float;
    end;
$$;

select f(0);

NOTICE:  oops 22012
 f 
---
 0
(1 row)

Read more about Errors and Messages and Trapping Errors.

answered Jun 17, 2017 at 15:58

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to print code, type of PostgreSQL / plpgsql exception

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related