How to do jUnit testing by PostgreSQL?

Question

I found some packages like pgTAP but I would like to get community supported tools. I am using PostgreSQL 9.4.3 in Debian 8.1. I have many kinds of tests which I would like to do:

• INSERT by purpose wrong thing and demand false
• INSERT correctly but demand not doing change to the database

How can you do simple jUnit testing by PostgreSQL?

Answer 1

This is an updated copy of my answer on StackOverflow.

If I have a function public.foo(bar text) returns text...

I create another function like this:

create or replace function test_foo() returns void as $$
begin

  perform assert_equals('stuff', foo('thing'));
  perform assert_null(foo(null));
  ...

end $$ language plpgsql;

I have some assert functions like the following. I purposely used the same names and signatures as JUnit.

CREATE OR REPLACE FUNCTION assert_equals(expected text, actual text) RETURNS void AS $$
begin
        if expected = actual or (expected is null and actual is null) then
            --do nothing
        else
            raise exception 'Assertion Error. Expected <%> but was <%>', expected, actual;
        end if;

end $$ LANGUAGE plpgsql;

I also have a function to run all tests:

CREATE OR REPLACE FUNCTION run_all_tests() RETURNS void AS $$
declare
    skip constant name[] = '{run_all_tests}'; --a list of functions to skip
    test_prefix constant name = 'test_';
    proc pg_catalog.pg_proc%rowtype;
    started timestamptz;
begin

    raise notice 'Time(m)   Name';
    for proc in select * from pg_catalog.pg_proc where proname ilike test_prefix || '%' and not proname = any(skip) order by proname loop
        started = clock_timestamp();
        execute format('select %s();', proc.proname);
        raise notice '% %()', to_char(clock_timestamp() - started, 'MI:SS:MS'), proc.proname;
    end loop;  

end $$ LANGUAGE plpgsql;

If you want to not commit data, start a transaction (BEGIN) then roll it back (ROLLBACK).

Looks like you can check code coverage with https://github.com/kputnam/piggly

Also look into:

http://pgtap.org/
http://en.dklab.ru/lib/dklab_pgunit/
http://www.epictest.org/