Monday, March 3, 2014

Some Basic Unit Test Patterns In Postgres

Let's say you want to write a unit test for PostgreSQL that expects an exception. Here's one way to do it:
create table my_table (
  id int primary key,
  name text not null

create or replace function test.throws_exception_when_name_is_null() returns void as $$

  insert into my_table values (1, null);
  exception when not_null_violation then return; --swallow the exception, because we are hoping for it. 

end $$ language plpgsql;

select test.throws_exception_when_name_is_null();
Some other common exception names:
  • foreign_key_violation
  • unique_violation
  • check_violation
See the full list of exceptions

Let's say you want to run a timed test. Throw an exception if the test takes too long:
create or replace function a_long_running_function() returns void as $$

    perform pg_sleep(2);

end $$ language plpgsql;

create or replace function test.test_timed() returns void as $$

    perform a_long_running_function();

end $$ language plpgsql set statement_timeout to 1900;

select test.test_timed();