PgTest

Testing in PostgreSQL for versions 9.4, 9.5 and 9.6.

Installation

Create database and execute pgtest.sql. This will create pgtest schema with functions that are used for testing.

curl --silent https://raw.githubusercontent.com/raitraidma/pgtest/master/pgtest.sql |\
sudo -u postgres psql mydatabasefortesting

To run PgTest's tests execute pgtest_test.sql. There you can also see how to use PgTest.

Usage

Create schema for your tests:

CREATE SCHEMA IF NOT EXISTS test;

Create test functions. Test function MUST return void and start with test_. Tests are ordered by function name.

CREATE OR REPLACE FUNCTION test.test_a_equals_a_ok()
  RETURNS void AS
$$
BEGIN
  PERFORM pgtest.assert_equals('A', 'A');
END
$$ LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path=test, pg_temp;

CREATE OR REPLACE FUNCTION test.test_a_equals_b_fails()
  RETURNS void AS
$$
BEGIN
  PERFORM pgtest.assert_equals('A', 'B');
END
$$ LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path=test, pg_temp;

Test, if function raises an error. Like @Test(expected=Exception.class) in JUnit:

CREATE OR REPLACE FUNCTION test.test_do_something()
  RETURNS void AS
$$
DECLARE
  b_raised_an_error BOOLEAN := FALSE;
BEGIN
  BEGIN
    PERFORM myschema.do_something();
  EXCEPTION
    WHEN OTHERS THEN b_raised_an_error := TRUE;
  END;
  PERFORM pgtest.assert_true(b_raised_an_error, 'Function myschema.do_something() should raise an error when ...');
END
$$ LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path=test, pg_temp;

Run tests:

SELECT pgtest.run_tests('test');
-- OR
SELECT pgtest.run_tests(ARRAY['test_schema1','test_schema2']); -- Run tests from multiple schemas.
-- OR
SELECT pgtest.run_tests_like('test_schema%'); -- Run tests whose schema is LIKE 'test_schema%'

Result is number of messages that failed. Raised messages show more specific info about tests - what tests ran, how many failed, what was the cause and how long it took. If you do not want to see pg_exception_context in messages then change client_min_messages to NOTICE. Otherwise use DEBUG, LOG or INFO.

SET client_min_messages TO NOTICE;

When using psql then you can hide CONTEXT info by using:

\set VERBOSITY terse

Assertions

• pgtest.assert_equals(expected_value, real_value [, custom_error_message]);
• pgtest.assert_not_equals(not_expected_value, real_value [, custom_error_message]);
• pgtest.assert_true(boolean_value [, custom_error_message]);
• pgtest.assert_false(boolean_value [, custom_error_message]);
• pgtest.assert_null(value [, custom_error_message]);
• pgtest.assert_not_null(value [, custom_error_message]);
• pgtest.assert_rows(expected_result_query, actual_result_query [, custom_error_message])
• pgtest.assert_table_exists(schema_name, table_name [, custom_error_message])
• pgtest.assert_table_does_not_exist(schema_name, table_name [, custom_error_message])
• pgtest.assert_temp_table_exists(table_name [, custom_error_message])
• pgtest.assert_temp_table_does_not_exist(table_name [, custom_error_message])
• pgtest.assert_view_exists(schema_name, view_name [, custom_error_message])
• pgtest.assert_view_does_not_exist(schema_name, view_name [, custom_error_message])
• pgtest.assert_mat_view_exists(schema_name, materialized_view_name [, custom_error_message])
• pgtest.assert_mat_view_does_not_exist(schema_name, materialized_view_name [, custom_error_message])
• pgtest.assert_relation_has_column(schema_name, relation_name, column_name [, custom_error_message])
• pgtest.assert_relation_does_not_have_column(schema_name, relation_name, column_name [, custom_error_message])
• pgtest.assert_function_exists(schema_name, function_name [, function_argument_types [, custom_error_message]]);
• pgtest.assert_function_does_not_exist(schema_name, function_name [, function_argument_types [, custom_error_message]]);
• pgtest.assert_extension_exists(extension_name [, custom_error_message]);
• pgtest.assert_extension_does_not_exist(extension_name [, custom_error_message]);
• pgtest.assert_column_type(schema_name, relation_name, column_name, expected_column_type [, custom_error_message]);
• pgtest.assert_not_column_type(schema_name, relation_name, column_name, not_expected_column_type [, custom_error_message]);
• pgtest.assert_table_has_fk(schema_name, table_name, constraint_name [, custom_error_message]);
• pgtest.assert_table_has_not_fk(schema_name, table_name, constraint_name [, custom_error_message]);

expected_value and real_value must be same type (base type or array).

expected_result_query and actual_result_query are sql queries (SELECT, VALUES, EXECUTE or table name).

function_argument_types is array of argument types (e.g ARRAY['character varying', 'integer']::VARCHAR[]. Default value is ARRAY[]::VARCHAR[]).

expected_column_type and not_expected_column_type must be SQL name of a data type.

Mock and spy

• pgtest.mock(original_function_schema_name, original_function_name, function_argument_types, mock_function_schema_name, mock_function_name). All parameters but function_argument_types are VARCHAR type. function_argument_types is array of VARCHAR. Values in function_argument_types must be SQL names of a data types (must match with values in column data_type in table information_schema.parameters). This function returns mock_id (VARCHAR) that can be used to assert mock function calls. Original function's implementation is changed with mock function's implementation.
• pgtest.spy(original_function_schema_name, original_function_name, function_argument_types). All matching parameters and return value are the same as pgtest.mock has. Only difference is that original function's implementation is not changed.
• pgtest.assert_called(mock_id [, expected_times_called [, custom_error_message]]) - mock_id is value returned by pgtest.mock or pgtest.spy. expected_times_called tells how many times we expect the mock/spy function to be called (by default 1).
• pgtest.assert_called_at_least_once(mock_id [, custom_error_message]) - mock_id is value returned by pgtest.mock or pgtest.spy. Expect that mock/spy function was called at least once.
• pgtest.assert_called_with_arguments(mock_id, expected_arguments, call_time [, custom_error_message]) - mock_id is value returned by pgtest.mock or pgtest.spy. expected_arguments tells what are the expected arguments (e.g ARRAY['a', '1']). call_time tells against which function call is tested (specifies the order of function calls).
• pgtest.assert_called_with_arguments(mock_id, expected_arguments [, custom_error_message]) - mock_id is value returned by pgtest.mock or pgtest.spy. expected_arguments tells what are the expected arguments (e.g ARRAY['a', '1']).

Helpers

• pgtest.remove_table_fk_constraints(schema_name, table_name) - removes all foreign key constraints of given table.

Hooks

• before() - runs before every test that's in the same schema.
• after() - runs after every test that's in the same schema.

Coverage

Checks if function is mentioned (could not detect, if it is actually called) in test function.

• pgtest.coverage(function_schemas, test_schemas) - function_schemas is VARCHAR[] and tells what schemas contain functions which existence in tests we want to check. test_schemas is VARCHAR[] and tells what schemas contain test functions.

-- Example:
SELECT * FROM pgtest.coverage(ARRAY['public']::VARCHAR[], ARRAY['tests']::VARCHAR[]);

Data types

You can retrieve all data types from:

SELECT DISTINCT (CASE
  WHEN p.data_type = 'ARRAY' THEN et.data_type::VARCHAR || '[]'
  WHEN p.data_type = 'USER-DEFINED' THEN p.udt_schema || '.' || p.udt_name
  ELSE p.data_type::VARCHAR
END) AS parameter_data_type
FROM information_schema.parameters p
LEFT JOIN information_schema.element_types et ON (
 (p.specific_catalog, p.specific_schema, p.specific_name, 'ROUTINE', p.dtd_identifier)
= (et.object_catalog, et.object_schema, et.object_name, et.object_type, et.collection_type_identifier)
)

In case of array, just add [] at the end (eg. character varying[]).

Some data types
bytea
real
bigint
smallint
bit
double precision
bit varying
timestamp without time zone
time with time zone
boolean
numeric
json
jsonb
integer
date
interval
timestamp with time zone
character
money
character varying
int4range
daterange
time without time zone
abstime
text

Limitations

• By default, function's name max length is 63 chars in PostgreSQL. If name is longer, then it will be truncated to 63 chars. This means if you have 2 functions with same parameters and where first 63 chars of the name are the same, then the second function will replace the first one.

Alternatives

• PGUnit 1
• PGUnit 2
• plpgunit
• pgTAP
• Dis
• Epic