pgTAP

https://pgtap.org/documentation.html
Admin
pgTAP is a unit testing framework for PostgreSQL, written in PL/pgSQL and PL/SQL, offering a...
Tokens:107,677
Snippets:812
Trust Score:8.7
Update:3 months ago
Show doc for...
Tokens:
Raw
### Define xUnit Setup Function

Source: https://pgtap.org/documentation.html

Define a setup function for xUnit-style tests. This function runs before tests and can include assertions or data setup.

```plpgsql
CREATE OR REPLACE FUNCTION setup_insert(
) RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is( MAX(nick), NULL, 'Should have no users') FROM users;
    INSERT INTO users (nick) VALUES ('theory');
END;
$$ LANGUAGE plpgsql;

```

--------------------------------

### Example: Table widgets does not exist

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the table in question does not exist or is not visible.

```sql
# Failed test 16: "Role slim should be granted SELECT on widgets"
#     Table widgets does not exist
```

--------------------------------

### Example: Role slim does not exist

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the role does not exist.

```sql
# Failed test 17: "Role slim should be granted SELECT on widgets"
#     Role slim does not exist
```

--------------------------------

### Check Function Language Examples

Source: https://pgtap.org/documentation.html

Examples demonstrating `function_lang_is` with different parameter combinations, including specifying schema, arguments, and language.

```sql
SELECT function_lang_is( 'myschema', 'foo',  ARRAY['integer', 'text'], 'plperl' );
```

```sql
SELECT function_lang_is( 'do_something', 'sql' );
```

```sql
SELECT function_lang_is( 'do_something', ARRAY['integer'], 'plpgsql' );
```

```sql
SELECT function_lang_is( 'do_something', ARRAY['numeric'], 'plpgsql' );
```

--------------------------------

### Example: Role does not exist for sequence privileges

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the role does not exist for sequence privilege tests.

```sql
# Failed test 17: "Role slim should be granted SELECT on seq_widgets"
#     Role slim does not exist
```

--------------------------------

### Example: Sequence does not exist for sequence privileges

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the sequence in question does not exist or is not visible for sequence privilege tests.

```sql
# Failed test 16: "Role slim should be granted SELECT on seq_widgets"
#     Sequence widgets does not exist
```

--------------------------------

### Example: Table does not exist for column privileges

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the table in question does not exist or is not visible for column privilege tests.

```sql
# Failed test 16: "Role slim should be granted SELECT on columns in widgets"
#     Table widgets does not exist
```

--------------------------------

### Example: Role kurk should be granted INSERT, UPDATE on widgets

Source: https://pgtap.org/documentation.html

This is an example diagnostic output indicating missing INSERT and UPDATE privileges for a role on a table.

```sql
# Failed test 15: "Role kurk should be granted SELECT, INSERT, UPDATE on widgets"
#     Missing privileges:
#           INSERT
#           UPDATE
```

--------------------------------

### Build and Install pgTAP from Source

Source: https://pgtap.org/documentation.html

These commands are used to build and install pgTAP from its source code after downloading and extracting it. Ensure you are in the extracted folder.

```bash
make
make install
make installcheck
```

--------------------------------

### Example: Role missing SELECT, UPDATE on seq_widgets

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when a role is not granted some of the specified permissions for sequence privileges.

```sql
# Failed test 15: "Role kurk should be granted USAGE on seq_widgets"
#     Missing privileges:
#           SELECT
#           UPDATE
```

--------------------------------

### Example: Role does not exist for column privileges

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when the role does not exist for column privilege tests.

```sql
# Failed test 17: "Role slim should be granted SELECT on columns in widgets"
#     Role slim does not exist
```

--------------------------------

### Install pgTAP from pg_config --sharedir

Source: https://pgtap.org/documentation.html

Install pgTAP using the `pgtap.sql` script located in the `contrib` directory, accessible via `pg_config --sharedir`. This method ensures you are using the correct version of the script for your PostgreSQL installation.

```bash
psql -d template1 -f `pg_config --sharedir`/contrib/pgtap.sql
```

--------------------------------

### Example: Role granted SELECT, UPDATE on columns in widgets

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when a role is granted permissions other than those specified for column privileges.

```sql
# Failed test 14: "Role bob should be granted SELECT on columns in widgets"
#     Extra privileges:
#           UPDATE
#           USAGE
```

--------------------------------

### Check Function Existence Example

Source: https://pgtap.org/documentation.html

An example of using `can` to check for the existence of multiple functions within the 'pg_catalog' schema.

```sql
SELECT can( 'pg_catalog', ARRAY['upper', 'lower'] );
```

--------------------------------

### Install pgTAP using SQL Script (Pre-9.1.0)

Source: https://pgtap.org/documentation.html

For PostgreSQL versions prior to 9.1.0, install pgTAP by running the `pgtap.sql` script. This script is typically located in the PostgreSQL sharedir's contrib directory.

```bash
psql -d mydb -f /path/to/pgsql/share/contrib/pgtap.sql
```

--------------------------------

### Example: Role missing SELECT, UPDATE on columns in widgets

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when a role is not granted some of the specified permissions for column privileges.

```sql
# Failed test 15: "Role kurk should be granted SELECT, INSERT, UPDATE on columns in widgets"
#     Missing privileges:
#           INSERT
#           UPDATE
```

--------------------------------

### Install pgTAP to template1 Database

Source: https://pgtap.org/documentation.html

Make pgTAP available to all new databases by installing it into the `template1` database. This ensures that new databases automatically include pgTAP functionality.

```bash
psql -d template1 -C "CREATE EXTENSION pgtap"
```

--------------------------------

### Create pgTAP Extension

Source: https://pgtap.org/documentation.html

After installing pgTAP, add it to a database by connecting as a superuser and running this command.

```sql
CREATE EXTENSION pgtap;
```

--------------------------------

### Install pgTAP on Debian/Ubuntu

Source: https://pgtap.org/documentation.html

Use this command to install pgTAP on Debian, Ubuntu, or Linux Mint systems using the apt package manager.

```bash
sudo apt-get install pgtap
```

--------------------------------

### Example: Role granted extra UPDATE, USAGE on seq_foo_id

Source: https://pgtap.org/documentation.html

This is an example diagnostic output when a role is granted permissions other than those specified for sequence privileges.

```sql
# Failed test 14: "Role bob should be granted SELECT on seq_foo_id"
#     Extra privileges:
#           UPDATE
#           USAGE
```

--------------------------------

### pgTAP Test Script Example

Source: https://pgtap.org/documentation.html

An example of a pgTAP test script designed to be run with `psql`. It configures output formatting, sets up transaction rollback, loads pgTAP functions, plans tests, runs a test, and finishes.

```sql
\unset ECHO
\set QUIET 1
-- Turn off echo and keep things quiet.

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager off

-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

-- Load the TAP functions.
BEGIN;
\i pgtap.sql

-- Plan the tests.
SELECT plan(1);

-- Run the tests.
SELECT pass( 'My test passed, w00t!' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;
```

--------------------------------

### set_eq() with Array Argument Example

Source: https://pgtap.org/documentation.html

Example of using set_eq() when the first query returns a single column, and the second argument is an array of values.

```sql
SELECT set_eq(
    'SELECT * FROM active_user_ids()',
    ARRAY[ 2, 3, 4, 5]
);
```

--------------------------------

### Install pgTAP into a Specific Schema (SCHEMA Clause)

Source: https://pgtap.org/documentation.html

Alternatively, use the SCHEMA clause within the CREATE EXTENSION command to specify the target schema for pgTAP installation.

```sql
CREATE EXTENSION pgtap SCHEMA tap;
```

--------------------------------

### Example Usage of performs_within()

Source: https://pgtap.org/documentation.html

This example demonstrates how to use performs_within() with a prepared statement, specifying the target average time, allowed variance, number of iterations, and a descriptive message.

```sql
PREPARE fast_query AS SELECT id FROM try WHERE name = 'Larry';
SELECT performs_within(
    'fast_query',
    250,
    10,
    100,
    'A select by name should be fast'
);
```

--------------------------------

### Install pgTAP into a Specific Schema (PGOPTIONS)

Source: https://pgtap.org/documentation.html

Use the PGOPTIONS environment variable to specify a schema when installing pgTAP and its supporting objects into a particular schema.

```bash
PGOPTIONS=--search_path=tap psql -d mydb -f pgTAP.sql
```

--------------------------------

### Run pgTAP Tests in Docker

Source: https://pgtap.org/documentation.html

Set up and run pgTAP tests within a local Docker environment. This involves building the Docker image, starting the PostgreSQL container, and executing tests using `make installcheck` or the `run` script.

```bash
cd test
docker compose build test
# start the postgres server in a docker container in the background
docker compose up -d test
# run the regression tests
docker compose exec test make install installcheck
# run the tests with pg_prove
# "run" builds and installs pgTAP, runs "CREATE EXTENSION"
# and then runs make test
docker compose exec test run
# Shut down the postgres container
docker compose down
```

--------------------------------

### Run pgTAP Tests with pg_prove

Source: https://pgtap.org/documentation.html

Use `pg_prove` to run multiple test scripts or xUnit test functions at once. This example shows a basic test script structure.

```sql
-- Start transaction and plan the tests.
BEGIN;
SELECT plan(1);

-- Run the tests.
SELECT pass( 'My test passed, w00t!' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK;

```

--------------------------------

### Build pgTAP with Custom pg_config Path

Source: https://pgtap.org/documentation.html

If pg_config is not found in your PATH, use this command to specify its location during the build and installation process.

```bash
env PG_CONFIG=/path/to/pg_config make && make install && make installcheck
```

--------------------------------

### Diagnose Missing Functions

Source: https://pgtap.org/documentation.html

Example diagnostic output when `can` fails because one or more specified functions are missing from the schema.

```text
# Failed test 52: "Schema pg_catalog can"
#     pg_catalog.foo() missing
#     pg_catalog.bar() missing
```

--------------------------------

### Build pgTAP with GNU make

Source: https://pgtap.org/documentation.html

If you encounter 'Need an operator' errors, you might need to use GNU make. This command uses 'gmake' to build and install pgTAP.

```bash
gmake
gmake install
gmake installcheck
```

--------------------------------

### Run All Tests with runtests()

Source: https://pgtap.org/documentation.html

Use `runtests()` to plan, execute, and finish all test functions in a schema. It supports schema and pattern arguments for filtering tests. Startup, setup, teardown, and shutdown functions are also supported.

```sql
SELECT runtests( :schema, :pattern );
```

```sql
SELECT runtests( :schema );
```

```sql
SELECT runtests( :pattern );
```

```sql
SELECT runtests( );
```

```sql
SELECT * FROM runtests( 'testschema', '^test' );
```

```sql
SELECT * FROM runtests('testschema'::name);
```

--------------------------------

### pgtap_version()

Source: https://pgtap.org/documentation.html

Retrieves the installed version of pgTAP.

```APIDOC
## pgtap_version()

### Description
Returns the version of pgTAP installed in the server as a NUMERIC value.

### Usage
```sql
SELECT pgtap_version();
```

### Response
*   `NUMERIC` - The version of pgTAP.
```

--------------------------------

### Test for older PostgreSQL versions with col_default_is()

Source: https://pgtap.org/documentation.html

This example shows how to handle different representations of functional defaults for CURRENT_USER and similar functions across PostgreSQL versions, using pg_version_num().

```sql
SELECT col_default_is( 'widgets', 'created_by', '"current_user"()' );
```

--------------------------------

### Diagnose Non-existent Function

Source: https://pgtap.org/documentation.html

Example diagnostic output when `function_lang_is` fails because the specified function does not exist.

```text
# Failed test 212: "Function myschema.grab() should be written in sql"
#     Function myschema.grab() does not exist
```

--------------------------------

### Define xUnit Test Function

Source: https://pgtap.org/documentation.html

Define an xUnit-style test function that returns TAP values using pgTAP assertions. This example tests a 'users' table.

```sql
CREATE OR REPLACE FUNCTION test_user(
) RETURNS SETOF TEXT AS $$
   SELECT is( nick, 'theory', 'Should have nick') FROM users;
$$ LANGUAGE sql;

```

--------------------------------

### pgTAP performs_within() Failure Output Example

Source: https://pgtap.org/documentation.html

This shows the typical output format when a performs_within() test fails, indicating the test number, description, actual average runtime, and the desired performance range.

```text
# Failed test 19: "The lookup should be fast!"
# average runtime: 210.266 ms
# desired average: 200 +/- 10 ms
```

--------------------------------

### Get pgTAP Version with pgtap_version()

Source: https://pgtap.org/documentation.html

Retrieves the installed pgTAP version as a NUMERIC value. This is useful for comparing against specific versions to conditionally run tests.

```SQL
SELECT pgtap_version();

```

```SQL
SELECT CASE WHEN pgtap_version() < 0.17
    THEN skip('No sequence assertions before pgTAP 0.17')
    ELSE has_sequence('my_big_seq')
    END;

```

--------------------------------

### Run pgTAP Tests with make

Source: https://pgtap.org/documentation.html

Execute the pgTAP test suite using the `make test` command. Ensure you are in the project directory and specify the PostgreSQL user if necessary.

```bash
make test PGUSER=postgres
```

--------------------------------

### Mix Prepared Statements and Cursors with results_eq()

Source: https://pgtap.org/documentation.html

Demonstrates using a prepared statement for one argument and a refcursor for the other in results_eq(). This highlights the flexibility in argument types.

```sql
PREPARE users_test AS SELECT * FROM active_users();
MOVE BACKWARD ALL IN chave;

SELECT results_eq(
    'users_test',
    'chave'::refcursor,
    'Gotta have those active users!'
);
```

--------------------------------

### Run Batched TAP Tests

Source: https://pgtap.org/documentation.html

Execute a batch of TAP tests by calling `plan()`, then the function containing the tests (e.g., `my_tests()`), and finally `finish()`.

```sql
SELECT plan(2);
```

```sql
SELECT * FROM my_tests();
```

```sql
SELECT * FROM finish();
```

--------------------------------

### Execute pgTAP Test Script with psql

Source: https://pgtap.org/documentation.html

Run a pgTAP test script using `psql`. The `-X` flag prevents reading of startup files, ensuring a clean execution environment. The output format is standard TAP.

```bash
% psql -d try -Xf test.sql
```

--------------------------------

### is() Failure Diagnostics

Source: https://pgtap.org/documentation.html

Example of detailed diagnostics produced by a failing is() test.

```text
# Failed test 17:  "Is foo the same as bar?"
#         have: waffle
#         want: yarblokos

```

--------------------------------

### hasnt_extension

Source: https://pgtap.org/documentation.html

Tests if a specified extension is NOT installed in a given schema. The test passes if the extension does not exist.

```APIDOC
## hasnt_extension

### Description
Tests if a specified extension is NOT installed in a given schema. The test passes if the extension does not exist.

### Signature
```sql
SELECT hasnt_extension( schema TEXT, extension TEXT, description TEXT );
SELECT hasnt_extension( schema TEXT, extension TEXT );
SELECT hasnt_extension( extension TEXT, description TEXT );
SELECT hasnt_extension( extension TEXT );
```

### Parameters
#### schema
(TEXT) - Schema in which the extension’s objects would be installed.

#### extension
(TEXT) - Name of an extension.

#### description
(TEXT) - A short description of the test.
```

--------------------------------

### todo_start() and todo_end()

Source: https://pgtap.org/documentation.html

These functions allow you to declare a block of tests as TODO tests. `todo_start()` begins the block, and `todo_end()` ends it. This is useful for managing groups of tests that are not yet ready or are known to be problematic.

```APIDOC
## todo_start()

### Description
Declares all subsequent tests as TODO tests until `todo_end()` is called.

### Usage
```sql
SELECT todo_start(message);
```

### Parameters
*   `message` (string) - Optional message describing the TODO tests.

## todo_end()

### Description
Stops running tests as TODO tests. This function is fatal if called without a preceding `todo_start()` call.

### Usage
```sql
SELECT todo_end();
```
```

--------------------------------

### Assert Extension Does Not Exist

Source: https://pgtap.org/documentation.html

Use `hasnt_extension()` to verify that a specific extension is not installed in a schema. The test passes if the extension is absent.

```sql
SELECT hasnt_extension( :schema, :extension, :description );
SELECT hasnt_extension( :schema, :extension );
SELECT hasnt_extension( :extension, :description );
SELECT hasnt_extension( :extension );
```

--------------------------------

### Assert Database Languages

Source: https://pgtap.org/documentation.html

Use `languages_are` to assert that only the specified procedural languages are installed in the database. A description for the test is optional.

```sql
SELECT languages_are( :languages, :description );
```

```sql
SELECT languages_are( :languages );
```

```sql
SELECT languages_are(ARRAY[ 'plpgsql', 'plperl', 'pllolcode' ]);
```

--------------------------------

### Test Database Privileges

Source: https://pgtap.org/documentation.html

Use `database_privs_are` to verify the privileges granted to a role for a specific database. The available privileges are CREATE, CONNECT, and TEMPORARY. The description parameter is optional.

```sql
SELECT database_privs_are ( :db, :role, :privileges, :description );
SELECT database_privs_are ( :db, :role, :privileges );
```

```sql
SELECT database_privs_are(
    'flipr', 'fred', ARRAY['CONNECT', 'TEMPORARY'],
    'Fred should be granted CONNECT and TEMPORARY on db "flipr"'
);
SELECT database_privs_are( 'dept_corrections', ARRAY['CREATE'] );
```

--------------------------------

### results_eq() Failure Diagnostic Message

Source: https://pgtap.org/documentation.html

Example of the diagnostic message provided by results_eq() when a test fails, indicating the differing row and its content.

```text
# Failed test 146
#     Results differ beginning at row 3:
#         have: (1,Anna)
```

--------------------------------

### Get PostgreSQL Version with pg_version()

Source: https://pgtap.org/documentation.html

Returns the server version number against which pgTAP was compiled. This stringified version is useful for compatibility checks.

```SQL
SELECT pg_version();

```

```SQL
try=% select current_setting( 'server_version'), pg_version();
 current_setting | pg_version
-----------------+------------
 12.2            | 12.2
(1 row)

```

--------------------------------

### Run xUnit Tests with pg_prove

Source: https://pgtap.org/documentation.html

Use `pg_prove` to execute all xUnit-style test functions within a database. This command targets tests for a specific database.

```bash
% pg_prove -d myapp --runtests

```

--------------------------------

### Diagnose Function Language Mismatch

Source: https://pgtap.org/documentation.html

Example diagnostic output when `function_lang_is` fails, indicating the actual language of the function versus the expected language.

```text
# Failed test 211: "Function mychema.eat(integer, text) should be written in perl"
#         have: plpgsql
#         want: perl
```

--------------------------------

### Upgrade pgTAP Extension

Source: https://pgtap.org/documentation.html

If you upgraded your PostgreSQL cluster to version 9.1 or later and had pgTAP installed, use this command to upgrade it to a properly packaged extension.

```sql
CREATE EXTENSION pgtap FROM unpackaged;
```

--------------------------------

### is() Record Comparison

Source: https://pgtap.org/documentation.html

Demonstrates using is() to compare entire records.

```sql
SELECT is( users.*, ROW(1, 'theory', true)::users )
  FROM users
 WHERE nick = 'theory';

```

--------------------------------

### Get Operating System Name with os_name()

Source: https://pgtap.org/documentation.html

Returns the name of the operating system on which pgTAP was compiled. This can be used to conditionally skip tests on specific OS environments.

```SQL
SELECT os_name();

```

--------------------------------

### Test Policy Command Type with policy_cmd_is()

Source: https://pgtap.org/documentation.html

Use policy_cmd_is to verify that a policy applies to the intended command type (SELECT, INSERT, UPDATE, DELETE, ALL). Schema can be omitted. Description is optional.

```sql
SELECT policy_cmd_is( :schema, :table, :policy, :command, :description );
SELECT policy_cmd_is( :schema, :table, :policy, :command );
SELECT policy_cmd_is( :table, :policy,  :command, :description );
SELECT policy_cmd_is( :table, :policy,  :command );
```

```sql
SELECT policy_cmd_is(
    'myschema',
    'atable',
    'apolicy'::NAME,
    'all'
);
```

--------------------------------

### Get PostgreSQL Version Number with pg_version_num()

Source: https://pgtap.org/documentation.html

Returns an integer representation of the PostgreSQL server version. This is useful for programmatically skipping tests based on version-specific features.

```SQL
SELECT pg_version_num();

```

```SQL
SELECT CASE WHEN pg_version_num() < 80300
    THEN skip('has_enum() not supported before 8.3' )
    ELSE has_enum( 'bug_status', 'mydesc' )
    END;

```

--------------------------------

### ok() Function Usage

Source: https://pgtap.org/documentation.html

Asserts a boolean condition, optionally with a description. Use for simple true/false checks.

```sql
SELECT ok( :boolean, :description );
SELECT ok( :boolean );

```

```sql
SELECT ok( 9 ^ 2 = 81,    'simple exponential' );
SELECT ok( 9 < 10,        'simple comparison' );
SELECT ok( 'foo' ~ '^f',  'simple regex' );
SELECT ok( active = true, name ||  widget active' )
  FROM widgets;

```

--------------------------------

### Diagnose Failed Index Type Test

Source: https://pgtap.org/documentation.html

This is an example of the diagnostic output when an `index_is_type` test fails, showing the actual index type found versus the expected type.

```text
# Failed test 175: "Index idx_bar should be a hash index"
#         have: btree
#         want: hash
```

--------------------------------

### Test Server Privileges with server_privs_are()

Source: https://pgtap.org/documentation.html

Verify role privileges on a server using `server_privs_are`. This function supports an optional description and provides detailed diagnostics for extra, missing, or non-existent privileges, servers, or roles.

```sql
SELECT server_privs_are ( :server, :role, :privileges, :description );
SELECT server_privs_are ( :server, :role, :privileges );
```

```sql
SELECT server_privs_are(
    'otherdb', 'fred', ARRAY['USAGE'],
    'Fred should be granted USAGE on server "otherdb"'
);
SELECT server_privs_are( 'myserv', ARRAY['USAGE'] );
```

```sql
# Failed test 14: "Role bob should be granted no privileges on myserv"
#     Extra privileges:
#           USAGE
```

```sql
# Failed test 15: "Role kurk should be granted USAGE on oltp"
#     Missing privileges:
#           USAGE
```

```sql
# Failed test 16: "Role slim should be granted USAGE on server oltp"
```

--------------------------------

### fk_ok()

Source: https://pgtap.org/documentation.html

Combines col_is_fk() and col_is_pk() to test for foreign key relationships between tables. It verifies that a foreign key constraint exists and points to the correct primary key. Supports various overloads for specifying schema, table, and column(s) for both foreign and primary keys, with an optional description.

```APIDOC
## fk_ok()

### Description
Tests for the existence of a foreign key relationship between two tables.

### Method
SQL Function

### Parameters
#### Path Parameters
- **fk_schema** (string) - Optional - Schema in which to find the table with the foreign key.
- **fk_table** (string) - Required - Name of a table containing the foreign key.
- **fk_columns** (array of strings or string) - Required - Array of the names of the foreign key columns or a single column name.
- **pk_schema** (string) - Optional - Schema in which to find the table with the primary key.
- **pk_table** (string) - Required - Name of a table containing the primary key.
- **pk_columns** (array of strings or string) - Required - Array of the names of the primary key columns or a single column name.
- **description** (string) - Optional - A short description of the test.

### Request Example
```sql
SELECT fk_ok( 'myschema', 'sometable', 'big_id', 'myschema', 'bigtable', 'id' );
SELECT fk_ok(
    'contacts',
    ARRAY['person_given_name', 'person_surname'],
    'persons',
    ARRAY['given_name', 'surname']
);
SELECT fk_ok( pg_my_temp_schema()::regnamespace::name, 'tmpa', 'id', pg_my_temp_schema()::regnamespace::name, 'tmpb', 'id' );
```

### Response
#### Success Response (200)
Returns true if the foreign key constraint is valid.

#### Response Example
```
-- No specific output on success, test passes silently.
```

#### Failure Example
```
# Failed test 178: "Column contacts(person_id) should reference persons(id)"
#         have: contacts(person_id) REFERENCES persons(id)"
#         want: contacts(person_nick) REFERENCES persons(nick)"
```
```

--------------------------------

### Run All xUnit Tests

Source: https://pgtap.org/documentation.html

Execute all defined xUnit test functions in the database using the `runtests()` function. Each test runs in its own transaction.

```sql
SELECT * FROM runtests();

```

--------------------------------

### Test Table Existence with VALUES

Source: https://pgtap.org/documentation.html

Use the `VALUES` command to create a relation for testing table existence across multiple schemas. The `format` function can be used to create descriptive test messages.

```sql
SELECT has_table(sch, ‘widgets’, format(‘Has %I.widgets’, sch)) FROM (VALUES(‘amazon’), (‘starbucks’), (‘boeing’)) F(sch);
```

--------------------------------

### Test for NULL default value with col_default_is()

Source: https://pgtap.org/documentation.html

This example demonstrates a failing test case for col_default_is() when attempting to test for a NULL default value without proper type casting.

```sql
SELECT col_default_is( 'tab', age, NULL );
```

--------------------------------

### Create a TAP Test Batch Function

Source: https://pgtap.org/documentation.html

Define a PostgreSQL function that `RETURNS SETOF TEXT` and uses `RETURN NEXT` to yield TAP test results. This allows batching multiple tests into a single function.

```plpgsql
CREATE OR REPLACE FUNCTION my_tests(
) RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT pass( 'plpgsql simple' );
    RETURN NEXT pass( 'plpgsql simple 2' );
END;
$$ LANGUAGE plpgsql;
```

--------------------------------

### Test Table Primary Key with has_pk()

Source: https://pgtap.org/documentation.html

Tests whether or not a table has a primary key. It can optionally include schema, table, and a description. If the schema is omitted, the table must be visible in the search path.

```sql
SELECT has_pk( :schema, :table, :description );
SELECT has_pk( :schema, :table );
SELECT has_pk( :table, :description );
SELECT has_pk( :table );
```

--------------------------------

### Test Table Existence (Case-Sensitive)

Source: https://pgtap.org/documentation.html

Use exact case matching when testing for objects created with double quotes. This is necessary for tables like 'Foo' created with `CREATE TABLE "Foo" (...)`.

```sql
SELECT has_table('Foo');
```

--------------------------------

### runtests() Function

Source: https://pgtap.org/documentation.html

The runtests() function executes all pgTAP test functions in a schema. It supports optional schema and pattern arguments to filter which tests are run. It handles test planning, execution, and completion, including startup, setup, teardown, and shutdown functions.

```APIDOC
## runtests()

### Description
Executes all pgTAP test functions within a specified schema, optionally filtering by a pattern. This function emulates xUnit testing environments by handling test planning, execution, and completion, including startup, setup, teardown, and shutdown functions.

### Function Signature
```sql
SELECT runtests( schema_name [, pattern ] );
```

### Parameters
#### Path Parameters
- **schema_name** (name) - Required - The name of the schema containing the pgTAP test functions.
- **pattern** (text) - Optional - A regular expression pattern to match against the names of the test functions.

### Example
```sql
-- Run all tests in the 'testschema' schema
SELECT runtests( 'testschema' );

-- Run tests in 'testschema' that match the pattern '^test'
SELECT runtests( 'testschema', '^test' );

-- Run all tests in the current schema
SELECT runtests( );

-- Run all tests in the 'testschema' schema, casting to name to avoid pattern interpretation
SELECT runtests('testschema'::name);
```

### Output Example
```
ok 1 - Startup test
# Subtest: public.test_this()
    ok 1 - simple pass
    ok 2 - another simple pass
ok 2 - public.test_this()
# Subtest: public.test_that()
    ok 1 - that simple
    ok 2 - that simple 2
ok 3 - public.test_that()
ok 4 - Shutdown test
1..4
```

### Fixture Functions
The following fixture functions are executed by `runtests()`:
- `^startup`: Functions whose names start with “startup” are run in alphabetical order before any test functions.
- `^setup`: Functions whose names start with “setup” are run in alphabetical order before each test function.
- `^teardown`: Functions whose names start with “teardown” are run in alphabetical order after each test function. They are not run after a test that has failed.
- `^shutdown`: Functions whose names start with “shutdown” are run in alphabetical order after all test functions have been run.

### Transactional Behavior
All tests executed by `runtests()` are run within a single transaction. Each test is run in a subtransaction that includes the execution of all setup and teardown functions. All transactions are rolled back after each test function and at the end of testing, leaving the database in its original state, with the exception of sequences.
```

--------------------------------

### Diagnostic Output for `do_tap()`

Source: https://pgtap.org/documentation.html

When `client_min_messages` is set to 'warning' or higher, `do_tap()` emits diagnostic messages showing the name of each function being called before execution.

```sql
# public.test_this()
ok 1 - simple pass
ok 2 - another simple pass
# public.test_that()
ok 3 - that simple
ok 4 - that simple 2
```

--------------------------------

### ok() Failure Diagnostics

Source: https://pgtap.org/documentation.html

Illustrates the diagnostic output when an ok() test fails.

```text
not ok 18 - sufficient mucus
#     Failed test 18: "sufficient mucus"


```

```text
not ok 18 - sufficient mucus
#     Failed test 18: "sufficient mucus"
#     (test result was NULL)

```

--------------------------------

### TAP Output with Descriptions

Source: https://pgtap.org/documentation.html

TAP output enhanced with test descriptions for clarity.

```text
  ok 4 - basic multi-variable
  not ok 5 - simple exponential
  ok 6 - force == mass * acceleration

```

--------------------------------

### results_eq() Function Signatures

Source: https://pgtap.org/documentation.html

Demonstrates the various ways to call the results_eq() function with different argument types like SQL, arrays, and cursors.

```sql
SELECT results_eq( :sql,    :sql,    :description );
SELECT results_eq( :sql,    :sql                  );
SELECT results_eq( :sql,    :array,  :description );
SELECT results_eq( :sql,    :array                );
SELECT results_eq( :cursor, :cursor, :description );
SELECT results_eq( :cursor, :cursor               );
SELECT results_eq( :sql,    :cursor, :description );
SELECT results_eq( :sql,    :cursor               );
SELECT results_eq( :cursor, :sql,    :description );
SELECT results_eq( :cursor, :sql                  );
SELECT results_eq( :cursor, :array,  :description );
SELECT results_eq( :cursor, :array                );
```

--------------------------------

### Declare TODO Test Block with todo_start()

Source: https://pgtap.org/documentation.html

Use todo_start() to mark subsequent tests as TODO until todo_end() is called. This helps in specifying the number of TODO tests within a block. TODO tests can be nested.

```SQL
SELECT todo_start('working on this');
-- lots of code
SELECT todo_start('working on that');
-- more code
SELECT todo_end();
SELECT todo_end();

```

```SQL
SELECT todo_start('working on this');
-- lots of code
SELECT todo('working on that', 2);
-- Two tests for which the above line applies
-- Followed by more tests scoped till the following line.
SELECT todo_end();

```

--------------------------------

### Test Foreign Key Constraint with fk_ok

Source: https://pgtap.org/documentation.html

Use fk_ok to combine col_is_fk() and col_is_pk() into a single test that verifies a foreign key relationship. The schema name is optional, and columns can be a string for a single column or an array of strings for multiple columns. The schema name for temporary tables can be specified using pg_my_temp_schema().

```sql
SELECT fk_ok( :fk_schema, :fk_table,   :fk_columns, :pk_schema,  :pk_table, :pk_columns, :description );
SELECT fk_ok( :fk_schema, :fk_table,   :fk_columns, :pk_schema,  :pk_table, :pk_columns );
SELECT fk_ok( :fk_table,  :fk_columns, :pk_table,   :pk_columns, :description );
SELECT fk_ok( :fk_table,  :fk_columns, :pk_table,   :pk_columns );
SELECT fk_ok( :fk_schema, :fk_table,   :fk_column,  :pk_schema,  :pk_table, :pk_column,  :description );
SELECT fk_ok( :fk_schema, :fk_table,   :fk_column,  :pk_schema,  :pk_table, :pk_column );
SELECT fk_ok( :fk_table,  :fk_column,  :pk_table,   :pk_column,  :description );
SELECT fk_ok( :fk_table,  :fk_column,  :pk_table,   :pk_column );
```

```sql
SELECT fk_ok( 'myschema', 'sometable', 'big_id', 'myschema', 'bigtable', 'id' );
SELECT fk_ok(
    'contacts',
    ARRAY['person_given_name', 'person_surname'],
    'persons',
    ARRAY['given_name', 'surname']
);
```

```sql
SELECT fk_ok(
    pg_my_temp_schema()::regnamespace::name, 'tmpa', 'id',
    pg_my_temp_schema()::regnamespace::name, 'tmpb', 'id'
);
```

```sql
SELECT fk_ok( 'contacts', 'person_id', 'persons', 'id' );
```
Tokens:
Raw