Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

Print unknown exceptions in PL/pgSQL

When trying to figure out why a function raised an exception you can print the error code raised to lookup in the table Appendix A-1.

One method is to capture others and then raise the magic sqlstate variable (only available in exception handlers)

create or replace function do_it(name text)
  returns void
as $$
begin
  select 42 from nothing;
exception
  when others then
    raise '%: %', sqlstate, sqlerrm;
end;
$$
  security definer
  language plpgsql
;

Then you can view the error:

select do_it('hi');
ERROR:  42P01: relation "nothing" does not exist
CONTEXT:  PL/pgSQL function do_it(text) line 6 at RAISE
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.