The example in the previous section has some more drawbacks. For example, if the function being tested raises warnings or notices, they will spoil the test protocol. Moreover, the testing code is not clean. The same pieces of code repeated several times, those BEGIN-END blocks are bulky, and the result protocol is not formatted very well. All these tasks could be automated using any of the unit test frameworks.

There is no unit test framework that comes out of the box with PostgreSQL, but there are several of them available from the community.

One of the most commonly used ones is pgtap (http://pgtap.org/). One can download it from GitHub (https://github.com/theory/pgtap/), compile and install it in the test database. The installation in a Linux system is quite easy and described well in the documentation. To compile and install the framework, the postgresql-server-dev-10 package needs to be installed. To install the framework in a particular database, you will need to create an extension in PostgreSQL, as follows:

car_portal=> CREATE EXTENSION pgtap;
CREATE EXTENSION

The tests are written as SQL scripts and they can be run in batches by the utility called pg_prove, which is provided with pgtap. There is also a way to write tests as stored functions using PL/pgSQL.

The pgtap framework provides the user with a set of helper functions that are used to wrap the testing code. They also write the results into a temporary table, which is used later to generate the testing protocol. For example, the ok() function reports a successful test if its argument is true, and a failed test, if not. The has_relation() function checks the database whether the specified relation exists. There are about a hundred of these functions.

The test scenario that was described in the preceding section can be implemented in the following script using pgtap:

-- Isolate test scenario in its own transaction
BEGIN;
-- report 2 tests will be run
SELECT plan(2);
-- Test 1. Call increment function
SELECT lives_ok('SELECT increment_counter()','Call increment function');
-- Test 2. Test results
SELECT is( (SELECT ARRAY [COUNT(*), MAX(counter)]::text FROM counter_table), ARRAY [1, 0]::text,'Check first record');
-- Report finish
SELECT finish();
-- Rollback changes made by the test
ROLLBACK;

The code is much cleaner now. This script is available in the attached media in the file, pgtap.sql. This is how it looks when the file is executed in the psql console. To make the test protocol look more compact, the Tuples only mode is switched on:

car_portal=> 	
Tuples only is on.
car_portal=> i pgtap.sql 
BEGIN
 1..2
 ok 1 - Call increment function
 ok 2 - Check first record
ROLLBACK

Another unit test framework that is worth mentioning is plpgunit. The tests are written as functions in PL/pgSQL. They use the provided helper functions to perform tests, for example, assert.is_equal() checks whether two arguments are equal. The helper functions format the results of the tests and display them on the console. The managing function unit_tests.begin() runs all the testing functions, logs their output into a table, and formats the results protocol.

The advantage of plpgunit is its simplicity—it is very lightweight and easy to install, and there is only one SQL script that you need to execute to get the framework in your database.

The plpgunit framework is available in GitHub at https://github.com/mixerp/plpgunit.