What is PGUnit?

PGUnit is a xUnit-style framework for stored procedures in PostgreSQL 8.3+. It allows
database developers to write automated tests for existed stored procedures or develop
procedures using concepts of Test Driven Development (TDD). All test cases are stored
in the database, so you don't need any external resources (like files, version control,
command-line utilities etc.) to save tests.

As in traditional xUnit, tests may be grouped in test case; each test-case may have
its own environment initialization code ("fixture preparation code", or setUp block).
The main benefit of PGUnit is that setUp block (usually quite CPU intensive) is
executed only once, and its effect is stored in a savepoint. Then, all tests
are executed from that savepoint, so the fixture initialization overheat is
minimal. All tests are still executed independently, because their effects are
automatically rolled back after the execution.

How to install

It's simple: just download and execute as usual SQL script. The new schema "pgunit"
will be created in you database; it contains all needed functions.

Why not pgTAP? Why yet another test framework?
Because pgTAP is not so simple and obvious as we need. But, possibly, you
should use pgTAP, not PGUnit - just learn it.

Test case sample

CREATE FUNCTION pgunit.test_sample () RETURNS testfunc[]
AS
$body$
SELECT pgunit.testcase(
$setUp$
-- setUp code is executed before ANY test function code (see below).
-- Effect of this execution is persistent only during the code
-- block execution and rolled back after the test is finished.
CREATE TABLE tst(id INTEGER);
$setUp$,
ARRAY[
-- This is a first test function code. Just check if we can insert
-- into the table created in setUp.
'first test: insert is okay', $sql$
INSERT INTO tst VALUES(1);
PERFORM pgunit.assert_same(1, (SELECT * FROM tst));
$sql$,
-- This is a second test function code.
-- Illustrates that the effect of the first function is not visible.
'second test: effect of previous function is not visible here', $sql$
PERFORM pgunit.assert_same(NULL, (SELECT * FROM tst));
$sql$,
-- This is a third test function code. Illustrate that we may use DECLARE.
'first test: you may use DECLARE in tests', $sql$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO tst VALUES(i);
PERFORM pgunit.assert_same(i, (SELECT * FROM tst WHERE id = i));
END LOOP;
END;
$sql$
]
);
$body$
LANGUAGE sql;

Result sample

Here is a sample of test output produced by SELECT pgunit.testrunner(NULL) call.
I advisedly broke one test to illustrate how it looks. The output format is PHPUnit-compatible.

Changelog

2008-11-09
- A little code simplification (undocumented "WHEN others THEN ..."
instead of exception class enumeration).
2008-11-07
- Great speed-up while running a bunch of test with a single setUp block.
In this case setUp is executed ONLY ONCE, and the effect of its
execution is stored in a savepoint which is used for all
individually-rollbacked tests. (All tests are still running
independently and are not intersected with each other.)
- Time measurnment of each test execution (thanks to Garrynja).
- Supress NOTICEs generated by a setUp or test code to keep the
execution log clean.
2008-11-03
- First release.