1

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?

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.