PolarDB:pgtap

Tables, indexes, and views

The following script checks whether specific tables, indexes, views, and materialized views 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;

Functions used:

Running the script against an empty database produces the following output, indicating the objects do not yet 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)

Create the 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;

All assertions now pass:

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)

RLS policies

The following script tests whether an RLS policy exists and applies to the correct roles. It also uses check_test() to verify that a test fails as expected—useful for validating negative cases.

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);

-- Check whether tap_policy applies to the SELECT command.
SELECT policy_cmd_is(
    'public',
    'tap_table',
    'tap_policy'::NAME,
    'select'
);

-- Check whether tap_policy applies to exactly tap_user_1 and tap_user_2.
SELECT policy_roles_are(
  'public',
  'tap_table',
  'tap_policy',
  ARRAY [
    'tap_user_1',
    'tap_user_2'
  ]
);

-- Check whether tap_table has tap_policy as its only policy.
SELECT policies_are(
  'public',
  'tap_table',
  ARRAY [
    'tap_policy'
  ]
);

-- Verify that a test fails when an unexpected role is specified.
SELECT * FROM check_test(
    policy_roles_are(
      'public',
      'tap_table',
      'tap_policy',
      ARRAY [
        'tape_user_1'
      ]),
    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;

Functions used:

Sample output for the policy existence checks:

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)

Columns

The following script checks whether columns exist and verifies their primary key and foreign key constraints:

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 not a foreign key (expected to fail).
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 col exists in tap_table.
SELECT has_column('tap_table', 'col');

-- Check whether non_col does not exist in tap_table.
SELECT hasnt_column('tap_table', 'non_col');

SELECT * FROM finish();
ROLLBACK;

DROP TABLE tap_table;

Functions used:

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)

Functions

The following script checks the return type of a function 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 (expected to fail).
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;

Functions used:

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)

Other database objects

pgTAP provides test functions for all major PostgreSQL object types, including tablespaces, schemas, tables, columns, views, sequences, indexes, triggers, functions, policies, users, languages, rules, operators, and extensions. The assertion pattern is the same as the examples above. For the full function reference, see the pgTAP documentation.