pgtap

Table, index, and view tests

The following script checks whether a set of database objects exist:

BEGIN;
SELECT plan(6);

-- Check whether the tap_table table exists.
SELECT has_table('tap_table');

-- Check whether the tap_table_non_exist table does not exist.
SELECT hasnt_table('tap_table_non_exist');

-- Check whether the tap_view view exists.
SELECT has_view('tap_view');

-- Check whether the materialized_tap_view materialized view exists.
SELECT has_materialized_view('materialized_tap_view');

-- Check whether the tap_table table has the tap_table_index index.
SELECT has_index('tap_table', 'tap_table_index');

-- Check whether tap_table is a relation.
SELECT has_relation('tap_table');

SELECT * FROM finish();
ROLLBACK;

Assertion functions used:

For full function signatures and optional parameters, see the pgTAP documentation.

Sample output when the objects do not exist:

has_table
-------------------------------------------------
 not ok 1 - Table tap_table should exist        +
 # Failed test 1: "Table tap_table should exist"
(1 row)
                    hasnt_table
---------------------------------------------------
 ok 2 - Table tap_table_non_exist should not exist
(1 row)
                   has_view
-----------------------------------------------
 not ok 3 - View tap_view should exist        +
 # Failed test 3: "View tap_view should exist"
(1 row)
                          has_materialized_view
-------------------------------------------------------------------------
 not ok 4 - Materialized view materialized_tap_view should exist        +
 # Failed test 4: "Materialized view materialized_tap_view should exist"
(1 row)
                         has_index
-------------------------------------------------------
 not ok 5 - Index tap_table_index should exist        +
 # Failed test 5: "Index tap_table_index should exist"
(1 row)
                      has_relation
----------------------------------------------------
 not ok 6 - Relation tap_table should exist        +
 # Failed test 6: "Relation tap_table should exist"
(1 row)
                finish
--------------------------------------
 # Looks like you failed 5 tests of 6
(1 row)

Lines starting with # are diagnostic messages that tell you exactly which test failed. The finish output summarizes how many tests passed.

Create the missing objects and run the test again:

CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
CREATE INDEX tap_table_index on tap_table(col);
CREATE VIEW tap_view AS SELECT * FROM tap_table;
CREATE MATERIALIZED VIEW materialized_tap_view AS SELECT * FROM tap_table;

Sample output after creating the objects:

has_table
-------------------------------------
 ok 1 - Table tap_table should exist
(1 row)
                    hasnt_table
---------------------------------------------------
 ok 2 - Table tap_table_non_exist should not exist
(1 row)
             has_view
-----------------------------------
 ok 3 - View tap_view should exist
(1 row)
                    has_materialized_view
-------------------------------------------------------------
 ok 4 - Materialized view materialized_tap_view should exist
(1 row)
                 has_index
-------------------------------------------
 ok 5 - Index tap_table_index should exist
(1 row)
              has_relation
----------------------------------------
 ok 6 - Relation tap_table should exist
(1 row)
 finish
--------
(0 rows)

An empty finish result means all assertions passed.

RLS policy tests

The following script checks whether a row-level security (RLS) policy is correctly defined on a table:

CREATE USER tap_user_1;
CREATE USER tap_user_2;
CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
CREATE POLICY tap_policy ON tap_table FOR select TO tap_user_1, tap_user_2;

BEGIN;
SELECT plan(5);
SELECT policy_cmd_is(
    'public',
    'tap_table',
    'tap_policy'::NAME,
    'select'
);
SELECT policy_roles_are(
  'public',
  'tap_table',
  'tap_policy',
  ARRAY [
    'tab_user_1', -- Check whether tab_user_1 is restricted by the RLS policy tap_policy.
    'tab_user_2' -- Check whether tab_user_2 is restricted by the RLS policy tap_policy.
  ]
);
SELECT policies_are(
  'public',
  'tap_table',
  ARRAY [
    'TAP_POLICE' -- Check whether tap_table contains the RLS policy tap_policy.
  ]
);
SELECT * FROM check_test(
    policy_roles_are(
      'public',
      'tap_table',
      'tap_policy',
      ARRAY [
        'tape_user_1' -- Verify that tab_user_1 is the only user restricted by tap_policy.
      ]),
    false,
    'check policy roles',
    'Policy tap_policy for table public.tap_table should have the correct roles');
SELECT * FROM finish();
ROLLBACK;

DROP POLICY tap_policy ON tap_table;
DROP TABLE tap_table;
DROP USER tap_user_1;
DROP USER tap_user_2;

Assertion functions used:

For full function signatures and optional parameters, see the pgTAP documentation.

Sample output:

policy_cmd_is
------------------------------------------------------------------------------------
 ok 1 - Policy tap_policy for table public.tap_table should apply to SELECT command
(1 row)
                                 policy_roles_are
-----------------------------------------------------------------------------------
 ok 2 - Policy tap_policy for table public.tap_table should have the correct roles
(1 row)
                          policies_are
----------------------------------------------------------------
 ok 3 - Table public.tap_table should have the correct policies
(1 row)
                          check_test
--------------------------------------------------------------
 ok 4 - check policy roles should fail
 ok 5 - check policy roles should have the proper description
(2 rows)
 finish
--------
(0 rows)

Column tests

The following script checks column properties — primary key, foreign key, and existence:

CREATE TABLE tap_table(col INT PRIMARY KEY, tap_desc TEXT);
CREATE INDEX tap_table_index ON tap_table(col);
CREATE UNIQUE INDEX tap_table_unique_index ON tap_table(col);

BEGIN;
SELECT plan(7);
-- Check whether col is the primary key of tap_table.
SELECT col_is_pk('tap_table', 'col');
-- Check whether tap_desc is not a primary key of tap_table.
SELECT col_isnt_pk('tap_table', 'tap_desc');
-- Verify that col_is_fk fails as expected (col is not a foreign key).
SELECT * FROM check_test(
    col_is_fk('tap_table', 'col'),
    false,
    'check foreign key of table',
    'Column tap_table(col) should be a foreign key');
-- Check whether col is not a foreign key of tap_table.
SELECT col_isnt_fk('tap_table', 'col');
-- Check whether tap_table contains the col column.
SELECT has_column('tap_table', 'col');
-- Check whether tap_table does not contain a non_col column.
SELECT hasnt_column('tap_table', 'non_col');
SELECT * FROM finish();
ROLLBACK;

DROP TABLE tap_table;

Assertion functions used:

For full function signatures and optional parameters, see the pgTAP documentation.

Sample output:

col_is_pk
------------------------------------------------------
 ok 1 - Column tap_table(col) should be a primary key
(1 row)
                          col_isnt_pk
---------------------------------------------------------------
 ok 2 - Column tap_table(tap_desc) should not be a primary key
(1 row)
                              check_test
----------------------------------------------------------------------
 ok 3 - check foreign key of table should fail
 ok 4 - check foreign key of table should have the proper description
(2 rows)
                       col_isnt_fk
----------------------------------------------------------
 ok 5 - Column tap_table(col) should not be a foreign key
(1 row)
                has_column
------------------------------------------
 ok 6 - Column tap_table.col should exist
(1 row)
                   hasnt_column
--------------------------------------------------
 ok 7 - Column tap_table.non_col should not exist
(1 row)
 finish
--------
(0 rows)

Function tests

The following script checks a function's return type and whether it is declared as SECURITY DEFINER:

CREATE OR REPLACE FUNCTION tap_function()
RETURNS text
AS $$
BEGIN
    RETURN 'This is tap test function';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION tap_function_bool(arg1 integer, arg2 boolean, arg3 text)
RETURNS boolean
AS $$
BEGIN
    RETURN true;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT plan(6);
-- Check whether tap_function returns text.
SELECT function_returns('tap_function', 'text');
-- Check whether tap_function_bool(integer, boolean, text) returns boolean.
SELECT function_returns('tap_function_bool', ARRAY['integer', 'boolean', 'text'], 'boolean');
-- Check whether tap_function is SECURITY DEFINER.
SELECT is_definer('tap_function');
-- Check whether tap_function_bool is not SECURITY DEFINER.
SELECT isnt_definer('tap_function_bool');
-- Verify that is_definer fails for tap_function_bool (it is not SECURITY DEFINER).
SELECT * FROM check_test(
    is_definer('tap_function_bool'),
    false,
    'check function security definer',
    'Function tap_function_bool() should be security definer');
SELECT * FROM finish();
ROLLBACK;

DROP FUNCTION tap_function;
DROP FUNCTION tap_function_bool;

Assertion functions used:

For full function signatures and optional parameters, see the pgTAP documentation.

Sample output:

function_returns
---------------------------------------------------
 ok 1 - Function tap_function() should return text
(1 row)
                                function_returns
---------------------------------------------------------------------------------
 ok 2 - Function tap_function_bool(integer, boolean, text) should return boolean
(1 row)
                        is_definer
-----------------------------------------------------------
 ok 3 - Function tap_function() should be security definer
(1 row)
                            isnt_definer
--------------------------------------------------------------------
 ok 4 - Function tap_function_bool() should not be security definer
(1 row)
                                check_test
---------------------------------------------------------------------------
 ok 5 - check function security definer should fail
 ok 6 - check function security definer should have the proper description
(2 rows)
 finish
--------
(0 rows)