PGUnit User Manual

1. Introduction to PGUnit

I developed PGUnit as a unit testing tool that is simple to install and use in PostgreSQL. It consists of a set of database functions designed to help developers validate database logic efficiently.

Files for PGUnit

The system includes the following core and supporting files:

• PGUnit.SQL: The core source code for the system.
• PGUnitUtils.sql: Utility functions for PGUnit.
• Sample/StudentsDatabase.sql: The test database used to demonstrate how the system works.
• Sample/StudentsDatabaseTestCases.sql: The test cases for the student database.
• Sample/PGUnitTests.sql: The test cases used to test PGUnit itself.

2. Installing PGUnit

To install PGUnit, execute the following steps:

1. Open the file called PGUnit.SQL in an editor like Notepad.
2. Copy all of the code.
3. Open your preferred SQL Client.
4. Open an SQL Window.
5. Paste the PGUnit code into the SQL Window.
6. Run the PGUnit source code.

Upon successful execution, you will see a query completion notice in the messages pane. Refreshing your SQL Client object browser will reveal a brand-new schema named pgunit.

Installing PGUnit Examples & Samples

To deploy the provided examples, sample testing environments, and baseline verifications, follow the instructions below for each specific sample file:

1. StudentsDatabase.sql

Purpose: This file builds the core schema, mock tables, and business logic for a simulated student database. It acts as the reference codebase used to safely demonstrate how PGUnit operates.

Installation: Open Sample/StudentsDatabase.sql in Notepad, copy all of the code, paste it into an open SQL Client window, and execute it.

2. StudentsDatabaseTestCases.sql

Purpose: This script installs the suite of functional test cases for the sample student database. It provides real-world examples of how to construct test functions, write assertions, and properly organise test blocks using the PGUnit markup syntax.

Installation: Open Sample/StudentsDatabaseTestCases.sql in Notepad, copy the entire block of code, paste it into your SQL Client window, and run it to install the test functions.

3. PGUnitTests.sql

Purpose: This script contains the internal test suite used to test PGUnit itself. The tests are run directly against the PGUnit code, serving as a comprehensive verification layer to guarantee the framework is operating correctly.

Installation: Open Sample/PGUnitTests.sql in Notepad, copy all of the code, paste it into an SQL window within your SQL Client, and execute it.

3. Explaining the Structure of a PGUnit Test Case

All PGUnit unit tests follow a strictly defined baseline structure. The name of the test function must start with test_ and follow the structure outlined below:

CREATE OR REPLACE FUNCTION pgunittests.test_one_setup_no_test_sections()
RETURNS TABLE(code VARCHAR) AS
$body$
    SELECT pgunit.testcase($$
        --@variables
        --@Rollback(TRUE/FALSE/T/F/YES/NO/Y/N)
        --@ExitOnFailFirstTest(TRUE/FALSE/T/F/YES/NO/Y/N)
        --@setup([description=<a description of the test>])
        --@test(description=<a description of the test>)
    $$);
$body$
LANGUAGE sql;

The Importance of SELECT pgunit.testcase($$...$$);

Test Markup and Sections

PGUnit has a specific markup that it uses to identify sections of your test cases:

• --@variables: If your test code uses variables, define those variables inside this section. This tag can only appear once per test function.
• --@Rollback(TRUE/FALSE/T/F/YES/NO/Y/N): This tells PGUnit if it must rollback data modifications applied during test execution. If set to TRUE, T, YES, or Y, the system automatically rolls back every modification made during the execution of the test cases in the function. Conversely, if configured to FALSE, F, NO, or N, modifications persist. If omitted entirely, it defaults to TRUE.This tag can only appear once per test function.
• --@ExitOnFailFirstTest(TRUE/FALSE/T/F/YES/NO/Y/N): Controls abort mechanics when multiple consecutive test sections reside in a single test function. If it is set to TRUE, T, YES, or Y, execution terminates the moment a test case in the function fails. If FALSE, F, NO, or N the tests continue to execute regardless of early validation errors. If unspecified, it defaults to FALSE. This tag can only appear once per test function.
• --@setup([description=<a description of the test>]): Contains all of the setup code that your test cases require. The description parameter is entirely optional. This tag can only appear once per test function.
• --@test(description=<a description of the test>): Defines Test case. Multiple test blocks may be specified in a test function to cleanly support test suites. Every test block must provide a description and you must supply a minimum of one active test section per test function.

4. Running PGUnit Tests

PGUnit uses the pgunit.testrunner function to execute test functions. This locates test functions, executes them and formats test results to closely replicate traditional XUnit layout conventions.

Function Parameters

• Schema Name : The specific database schema containing target test functions.
• Test Case Function Name : The exact target function name to execute. Passing only the schema name automatically executes all tests functions discovered in the specified schema.

Example 1: Running a Single Test

Run this statement directly inside your SQL Client to execute a specific test function:

SELECT * FROM pgunit.testrunner('pgunittraining', 'test_boundary_pass_result');

Example 2: Running Multiple Tests

Execute this statement to scan, collect, and automatically run every test case function declared within the specified schema:

SELECT * FROM pgunit.testrunner('pgunittraining');

5. Reading PGUnit Results

PGUnit provides two sets of test result information to your SQL Client:

1. The Test Results Table: This lists the number of test functions executed, the total number of test cases that are available, the number of test cases that were not executed and the number of test cases that passed/failed.
2. The Messages/Notice Information: Provides more information about the test cases that were executed.

The Test Results Table

The Test Results Table looks like this:

• test_case_functions: The number of test case functions executed.
• tests_cases: The total number of test cases contained across those functions.
• tests_cases_not_run: The total number of test cases that haven't been run (for instance, skipped due to exit abort flags).
• passed: The number of test cases that have successfully passed validation.
• failed: The number of test cases that have failed validation.

The Messages/Notice Information

The detailed information about test case execution looks like this:

postgres=# select * from pgunit.testrunner('pgunittraining');
NOTICE:  PGUnit Version 1.0 by John Clarke
NOTICE:  Test Function : pgunittraining.test_fail_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns a pass grade - OK (00:00:00.001 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_boundary_pass_result
NOTICE:  Test Cases in Function : 6 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns FAIL for a grade of 39 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 40 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 41 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 58 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 59 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 60 - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_pass_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns a pass grade - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_merit_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns a merit grade - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_distinction_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns a distinction grade - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_badgrade1_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade traps an invalid result of -1 - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_badgrade2_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST - Check that pgunittraining.getgrade traps an invalid result of -1 - Failed (00:00:00 ms)
NOTICE:  ERROR : TEST Section Failed : The Result is not VOID
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_badgrade3_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST : Check that pgunittraining.getgrade traps an invalid result of 101 - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_badgrade4_result
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  TEST - Check that pgunittraining.getgrade traps an invalid result of 101 - Failed (00:00:00 ms)
NOTICE:  ERROR : TEST Section Failed : The Result is not VOID
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_boundary_fail_result
NOTICE:  Test Cases in Function : 6 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns VOID for a grade of -1 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns FAIL for a grade of 0 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns FAIL for a grade of 1 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns FAIL for a grade of 38 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns FAIL for a grade of 39 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 40 - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_boundary_merit_result
NOTICE:  Test Cases in Function : 6 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns PASS for a grade of 59 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 60 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 61 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 78 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 79 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns DISTINCTION for a grade of 80 - OK (00:00:00 ms)
------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_boundary_distinction_result
NOTICE:  Test Cases in Function : 6 tests
NOTICE:  TEST : Check that pgunittraining.getgrade returns MERIT for a grade of 79 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns DISTINCTION for a grade of 80 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns DISTINCTION for a grade of 81 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns DISTINCTION for a grade of 99 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns DISTINCTION for a grade of 100 - OK (00:00:00 ms)
NOTICE:  TEST : Check that pgunittraining.getgrade returns VOID for a grade of 101 - OK (00:00:00 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_check_student_name
NOTICE:  Test Cases in Function : 2 tests
NOTICE:  TEST : Check that the firstname for the student with the id of 1 is Ila - OK (00:00:00.001 ms)
NOTICE:  TEST : Check that the surname for the student with the id of 1 is Barela - OK (00:00:00 ms)
------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_check_studentsgrades1
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  SETUP : Create the expected results table - OK (00:00:00.029 ms)
NOTICE:  TEST : Check that the expected_studentsgrades table matches the actual_studentsgrades table - OK (00:00:00.01 ms)
NOTICE:  ------------------------------------------------------
NOTICE:  Test Function : pgunittraining.test_check_studentsgrades2
NOTICE:  Test Cases in Function : 1 tests
NOTICE:  SETUP : Create the expected results table - OK (00:00:00.013 ms)
NOTICE:  TEST - Check that the expected_studentsgrades table matches the actual_studentsgrades table - Failed (00:00:00.004 ms)
NOTICE:  ERROR : TEST Section Failed : Data mismatch detected: Tables temp.expected_studentsgrades and temp.actual_studentsgrades are not identical. Found 1 differing record(s).
NOTICE:  ======================================================
NOTICE:  Total Execution Time: 00:00:00.304
NOTICE:  Total Number of functions with test cases : 15
NOTICE:  Total Number of Tests : 36
NOTICE:  Total Number of Tests not executed : 0
NOTICE:  Passed : 33
NOTICE:  Failed : 3

6. PGUnit Utility Functions

The following utility function is used to handle table comparisons.

PGUnitUtils.compare_tables

Description: Check to see if two tables contain matching content.

Parameters:

• in_expected : The name of the table containing the expected values.
• in_actual : The name of the table containing the actual values.

Example Syntax:

SELECT * FROM PGUnitUtils.compare_tables('temp.expected_studentsgrades', 'temp.actual_studentsgrades');

7. Contact the Developer

If you have questions, feedback, or require deployment support for PGUnit, feel free to get in touch:

• Developer: John Clarke
• Email: jclarke@digitaldimensions.co.uk
• Website: www.digitaldimensions.co.uk