5. The assert Extension

The assert extension grew out of a desire to construct a test suite using SQL statements alone. It can be installed and removed in the standard manner:

CREATE EXTENSION assert;
DROP EXTENSION assert;

It is a relocatable, pure SQL extension which therefore requires no external libraries or compilation, and consists entirely of user-callable functions.

5.1. Usage

The most basic routines in the extension are assert_is_null() and assert_is_not_null() which test whether the given value is or is not NULL respectively, raising SQLSTATEs UTA06 and UTA07 respectively. These functions have two overloaded variants, one using the polymorphic anyelement type and the other text which should cover the vast majority of use cases:

db=# SELECT assert_is_null(null::date);
 assert_is_null
----------------

(1 row)

db=# SELECT assert_is_null('');
ERROR:   is not NULL
db=# SELECT assert_is_null(1);
ERROR:  1 is not NULL

Similarly, assert_equals() and assert_not_equals() test whether the two provided values are equal or not. If the assertion fails, SQLSTATE UTA08 is raised by assert_equals() and UTA09 by assert_not_equals(). Again, two overloaded variants exist to cover all necessary types:

db=# SELECT assert_equals(1, 1);
 assert_equals
---------------

(1 row)

db=# SELECT assert_equals('foo', 'bar');
ERROR:  foo does not equal bar

A set of functions for asserting the existing of various structures are also provided: assert_table_exists() (which works for any relation-like structure such as tables and views), assert_column_exists() (for testing individual columns within a relation), assert_function_exists(), and assert_trigger_exists():

db=# CREATE TABLE foo (i integer NOT NULL);
CREATE TABLE
db=# SELECT assert_table_exists('foo');
 assert_table_exists
---------------------

(1 row)

db=# SELECT assert_table_exists('bar');
ERROR:  Table public.bar does not exist
CONTEXT:  SQL function "assert_table_exists" statement 1
db=# SELECT assert_column_exists('foo', 'i');
 assert_column_exists
----------------------

(1 row)

Note that with a bit of querying knowledge, it is actually more efficient to test a whole table structure using assert_equals(). For example:

CREATE TABLE bar (
    i integer NOT NULL PRIMARY KEY,
    j integer NOT NULL
);

SELECT assert_equals(4::bigint, (
    SELECT count(*)
    FROM (
        SELECT attnum, attname
        FROM pg_catalog.pg_attribute
        WHERE attrelid = 'bar'::regclass
        AND attnum > 0

        INTERSECT

        VALUES
            (1, 'i'),
            (2, 'j'),
    ) AS t));

Naturally, one could extend this technique to include tests for the column types, nullability, etc.

Finally, the assert_raises() function can be used to test whether arbitrary SQL raises an expected SQLSTATE. This is especially useful when building test suites for extensions (naturally, this function is used extensively within the test suite for the assert extension!):

db=# SELECT assert_raises('UTA08', 'SELECT assert_equals(1, 2)');
 assert_raises
---------------

(1 row)

db=# SELECT assert_raises('UTA08', 'SELECT assert_equals(1, 1)');
ERROR:  SELECT assert_equals(1, 1) did not signal SQLSTATE UTA08

5.2. API

assert.assert_equals(a, b)
Parameters:
  • a – The first value to compare
  • b – The second value to compare

Raises SQLSTATE ‘UTA08’ if a and b are not equal. If either a or b are NULL, the assertion will succeed (no exception will be raised). See assert_is_null() for this instead.

assert.assert_not_equals(a, b)
Parameters:
  • a – The first value to compare
  • b – The second value to compare

Raises SQLSTATE ‘UTA09’ if a and b are equal. If either a or b are NULL, the assertion will succeed (no exception will be raised). See assert_is_null() for this instead.

assert.assert_is_null(a)
Parameters:a – The value to test

Raises SQLSTATE ‘UTA06’ if a is not NULL.

assert.assert_is_not_null(a)
Parameters:a – The value to test

Raises SQLSTATE ‘UTA07’ if a is NULL.

assert.assert_table_exists(aschema, atable)
assert.assert_table_exists(atable)
Parameters:
  • aschema – The schema containing the table to test
  • atable – The table to test for existence

Tests whether the table named atable within the schema aschema exists. If aschema is omitted it defaults to the current schema. Raises SQLSTATE ‘UTA02’ if the table does not exist.

assert.assert_column_exists(aschema, atable, acolumn)
assert.assert_column_exists(atable, acolumn)
Parameters:
  • aschema – The schema containing the table to test
  • atable – The table containing the column to test
  • acolumn – The column to test for existence

Tests whether the column named acolumn exists in the table identified by aschema and atable. If aschema is omitted it defaults to the current schema. Raises SQLSTATE ‘UTA03’ if the column does not exist.

assert.assert_trigger_exists(aschema, atable, atrigger)
assert.assert_trigger_exists(atable, atrigger)
Parameters:
  • aschema – The schema containing the table to test
  • atable – The table containing the column to test
  • atrigger – The trigger to test for existence

Tests whether the trigger named atrigger exists for the table identified by aschema and atable. If aschema is omitted it defaults to the current schema. Raises SQLSTATE ‘UTA04’ if the column does not exist.

assert.assert_function_exists(aschema, atable, argtypes)
assert.assert_function_exists(atable, argtypes)
Parameters:
  • aschema – The schema containing the function to test
  • atable – The table to test for existence
  • argtypes – An array of type names to match against the parameters of the function

Tests whether the function named afunction with the parameter types given by the array argtypes exists within the schema aschema. If aschema is omitted it defaults to the current schema. Raises SQLSTATE ‘UTA05’ if the table does not exist.

assert.assert_raises(state, sql)
Parameters:
  • state – The SQLSTATE to test for
  • sql – The SQL to execute to test if it fails correctly

Tests whether the execution of the statement in sql results in the SQLSTATE state being raised. Raises SQLSTATE UTA01 in the event that state is not raised, or that a different SQLSTATE is raised.