### Build and Install pgTAP

Source: https://github.com/theory/pgtap/blob/main/README.md

Standard commands to build, install, and check the installation of pgTAP.

```sh
make
make install
make installcheck
```

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

### Build and Install pgTAP Extension

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Follow these steps to build and install the pgTAP extension from source.

```bash
cd /path/to/pgtap
make
make install
make installcheck
```

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

### Example Usage of matches()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Provides examples of using matches() to validate an email domain format and check if a version string starts with 'PostgreSQL'.

```sql
SELECT matches(email, '^.+@example\.com$', 'Email domain valid');
SELECT matches(version(), '^PostgreSQL', 'PostgreSQL version');
```

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

### pgTAP Basic Assertions and Setup

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Demonstrates the basic setup and assertion functions in pgTAP, including planning tests and various ways to assert equality or patterns.

```sql
CREATE EXTENSION IF NOT EXISTS pgtap;

SELECT plan( 23 );
-- or SELECT * from no_plan();

-- Various ways to say "ok"
SELECT ok( :have = :want, :test_description );

SELECT is(   :have, :want, :test_description );
SELECT isnt( :have, :want, :test_description );

-- Rather than \echo # here's what went wrong
SELECT diag( 'here\'s what went wrong' );

-- Compare values with LIKE or regular expressions.
SELECT alike(   :have, :like_expression, :test_description );
SELECT unalike( :have, :like_expression, :test_description );

SELECT matches(      :have, :regex, :test_description );
SELECT doesnt_match( :have, :regex, :test_description );

SELECT cmp_ok(:have, '=', :want, :test_description );

-- Skip tests based on runtime conditions.
SELECT CASE WHEN :some_feature THEN collect_tap(
    ok( foo(),       :test_description),
    is( foo(42), 23, :test_description)
) ELSE skip(:why, :how_many ) END;

-- Mark some tests as to-do tests.
SELECT todo(:why, :how_many);
SELECT ok( foo(),       :test_description);
SELECT is( foo(42), 23, :test_description);

-- Simple pass/fail.
SELECT pass(:test_description);
SELECT fail(:test_description);
```

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

### Define Setup and Test Functions

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Define setup and test functions for xUnit-style testing. The `setup_insert` function prepares the test environment, and `test_user` contains the actual test logic.

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

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

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

### Install pgTAP in GitHub Actions

Source: https://github.com/theory/pgtap/blob/main/_autodocs/README.md

Installs the pgTAP extension using pgxn. This is typically part of a CI/CD pipeline setup.

```yaml
- name: Install pgTAP
  run: pgxn install pgtap
- name: Run tests
  run: make installcheck
```

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

### xUnit-style Testing with Setup and Teardown

Source: https://github.com/theory/pgtap/blob/main/_autodocs/overview.md

Implements xUnit-style tests by defining setup, test, and teardown functions. This pattern is useful for managing test data and ensuring a clean state after tests.

```sql
CREATE FUNCTION setup() RETURNS SETOF TEXT AS $$
BEGIN
    CREATE TABLE test_data (id INT, name TEXT);
    INSERT INTO test_data VALUES (1, 'Alice'), (2, 'Bob');
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION test_data_exists() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is(
        (SELECT COUNT(*) FROM test_data)::integer,
        2,
        'Two rows in test_data'
    );
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION test_data_structure() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT columns_are('test_data', ARRAY['id', 'name']);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION teardown() RETURNS SETOF TEXT AS $$
BEGIN
    DROP TABLE test_data;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM runtests();
```

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

### pgTAP Test Script Example

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

An example of a pgTAP test script that sets variables for quiet output, starts a transaction, loads pgTAP functions, plans tests, runs a test, and rolls back the transaction.

```pgsql
\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;
```

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

### Install pgTAP into Template Database

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Install pgTAP into the 'template1' database to make it available to all new databases.

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

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

### SQL Examples for lives_ok

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Shows how to use the lives_ok function to assert that an SQL statement executes without throwing an exception. Includes examples with and without a descriptive message.

```sql
SELECT lives_ok( :sql, :description );
SELECT lives_ok( :sql );
```

```sql
SELECT lives_ok(
    'INSERT INTO try (id) VALUES (1)',
    'We should not get a unique violation for a new PK'
);
```

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

### Install pgTAP Extension

Source: https://github.com/theory/pgtap/blob/main/_autodocs/MANIFEST.txt

Provides the SQL command to install the pgTAP extension in a PostgreSQL database.

```sql
CREATE EXTENSION IF NOT EXISTS pgtap;
```

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

### Example Usage of doesnt_match()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Shows an example of using doesnt_match() to ensure an email address does not belong to a specific invalid domain.

```sql
SELECT doesnt_match(email, '@badomain\.com$', 'Not from bad domain');
```

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

### Example Usage of imatches()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Demonstrates using imatches() to check if a name starts with 'john', ignoring case.

```sql
SELECT imatches(name, '^john', 'Name starts with john (case-insensitive)');
```

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

### SQL Examples for performs_ok

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Illustrates the use of performs_ok to verify that an SQL statement executes within a specified time limit. Examples include providing a custom description and relying on a default one.

```sql
SELECT performs_ok( :sql, :milliseconds, :description );
SELECT performs_ok( :sql, :milliseconds );
```

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

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

### pgTAP sequence_privs_are() examples

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Examples demonstrating the usage of the sequence_privs_are function with and without the schema and description parameters.

```sql
SELECT sequence_privs_are(
    'public', 'seq_ids', 'fred', ARRAY['SELECT', 'UPDATE'],
    'Fred should be able to select and update seq_ids'
);
SELECT sequence_privs_are( 'seq_u', 'slim', ARRAY['USAGE'] );
```

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

### Example Usage of no_plan()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Shows how to use no_plan() when the exact number of tests is not predetermined, followed by a test assertion and finish().

```sql
SELECT * FROM no_plan();
SELECT ok(some_condition(), 'test description');
SELECT * FROM finish();
```

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

### pgTAP table_privs_are() examples

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Examples demonstrating the usage of the table_privs_are function with and without the schema and description parameters.

```sql
SELECT table_privs_are(
    'public', 'frobulate', 'fred', ARRAY['SELECT', 'DELETE'],
    'Fred should be able to select and delete on frobulate'
);
SELECT table_privs_are( 'widgets', 'slim', ARRAY['INSERT', 'UPDATE'] );
```

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

### Running pgTAP Tests with pg_prove

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Example output of running pgTAP tests using the pg_prove utility.

```console
% pg_prove -U postgres sql/*.sql
sql/coltap.....ok
sql/hastap.....ok
sql/moretap....ok
sql/pg73.......ok
sql/pktap......ok
All tests successful.
Files=5, Tests=216,  1 wallclock secs ( 0.06 usr  0.02 sys +  0.08 cusr  0.07 csys =  0.23 CPU)
Result: PASS
```

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

### Run Tests with pg_prove

Source: https://github.com/theory/pgtap/blob/main/_autodocs/README.md

Commands to install pg_prove and run test files or xUnit tests using the pg_prove utility.

```bash
# Install pg_prove from CPAN
cpan TAP::Parser::SourceHandler::pgTAP

# Run test files
pg_prove -d testdb sql/test_*.sql

# Run xUnit tests
pg_prove -d testdb --runtests
```

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

### Build and Install pgTAP with Custom pg_config Path

Source: https://github.com/theory/pgtap/blob/main/README.md

Specify the path to pg_config if it's not in the system's PATH.

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

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

### Example Usage of plan()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Demonstrates the typical flow of using plan() to set the test count, followed by individual test assertions and a finish() call.

```sql
SELECT plan(3);
SELECT ok(TRUE, 'first test');
SELECT ok(TRUE, 'second test');
SELECT ok(FALSE, 'third test');
SELECT * FROM finish();
```

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

### Example: Verifying pg_catalog.pg_type.typname is text

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

This example tests if the 'typname' column in 'pg_catalog.pg_type' is of type 'text' and shows the expected failure output format.

```sql
SELECT col_type_is( 'pg_catalog', 'pg_type', 'typname', 'text' );
```

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

### Test Extension Installation

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Use `has_extension` to check if a PostgreSQL extension is installed. You can provide the extension name and a description.

```sql
SELECT has_extension('pgtap', 'pgTAP installed');
SELECT has_extension('citext');
```

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

### Example usage of fk_ok() with specific tables and columns

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Demonstrates how to use the `fk_ok()` function with explicit schema, table, and column names for both the foreign and primary keys.

```sql
SELECT fk_ok( 'myschema', 'sometable', 'big_id', 'myschema', 'bigtable', 'id' );
```

```sql
SELECT fk_ok(
    'contacts',
    ARRAY['person_given_name', 'person_surname'],
    'persons',
    ARRAY['given_name', 'surname']
);
```

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

### Build and Install pgTAP within PostgreSQL Source Tree

Source: https://github.com/theory/pgtap/blob/main/README.md

Build and install pgTAP by copying it into the PostgreSQL source tree's contrib directory, useful when pg_config is unavailable.

```sh
env NO_PGXS=1 make && make install && make installcheck
```

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

### SQL Examples for throws_matching and throws_imatching

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Demonstrates the syntax for using throws_matching and throws_imatching functions to test SQL statements that are expected to throw exceptions, with options for case-insensitive matching and descriptions.

```sql
SELECT throws_matching(  :sql, :regex, :description );
SELECT throws_matching(  :sql, :regex );
SELECT throws_imatching( :sql, :regex, :description );
SELECT throws_imatching( :sql, :regex );
```

```sql
PREPARE my_thrower AS INSERT INTO try (tz) VALUES ('America/Moscow');
SELECT throws_matching(
    'my_thrower',
    '.+"timezone_check"',
    'We should error for invalid time zone'
);
```

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

### Install pgTAP Extension in Custom Prefix

Source: https://github.com/theory/pgtap/blob/main/README.md

Install the pgTAP extension into a custom directory prefix on PostgreSQL 18 or later.

```sh
make install prefix=/usr/local/extras
```

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

### Build and Install pgTAP with GNU Make

Source: https://github.com/theory/pgtap/blob/main/README.md

Commands to use when GNU make is required, often aliased as 'gmake'.

```sh
gmake
gmake install
gmake installcheck
```

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

### pgTAP Test Function Structure

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Defines setup, test, and teardown functions for pgTAP tests. The setup function runs before tests, test_ functions are the actual tests, and teardown runs after. Ensure test functions start with 'test_'.

```sql
CREATE FUNCTION setup() RETURNS SETOF TEXT AS $$
BEGIN
    CREATE TABLE test_table AS SELECT 1 AS id, 'test' AS name;
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION test_count() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is(
        (SELECT count(*) FROM test_table),
        1::bigint,
        'Table has one row'
    );
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION teardown() RETURNS SETOF TEXT AS $$
BEGIN
    DROP TABLE IF EXISTS test_table;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- Run all tests
SELECT * FROM runtests();
```

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

### Example Usage of ok()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Illustrates using the ok() assertion for simple comparisons, regular expression checks, and conditional checks on table data.

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

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

### check_test() with expected description

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

This example demonstrates how to test for an expected description output by your test function. This adds a second test to your plan.

```sql
SELECT * FROM check_test(
    lc_eq( ''this'', ''THIS'' ),
    true,
    'lc_eq() test',
    'this is THIS'
);
```

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

### Example Usage of isnt()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Shows examples of using isnt() to assert that a value is not a specific empty string and that a status column is not 'inactive'.

```sql
SELECT isnt(foo(), '', 'Got some foo');
SELECT isnt(status, 'inactive', 'Status is active');
```

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

### Test Schema Extensions

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Verify that the specified extensions are installed in the database.

```sql
SELECT extensions_are(ARRAY['citext', 'pgtap', 'uuid-ossp']);
```

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

### Test Installed Procedural Language with has_language

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Confirm that a specified procedural language is installed on the PostgreSQL server. An optional description can be provided for the test.

```sql
SELECT has_language('plpgsql', 'PL/pgSQL installed');
```

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

### Example of fk_ok() failure output

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Illustrates the diagnostic output produced by `fk_ok()` when a foreign key test fails, highlighting the expected versus actual constraint details.

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

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

### Create pgTAP Extension in Specific Schema

Source: https://github.com/theory/pgtap/blob/main/README.md

SQL command to install the pgTAP extension into a specified schema.

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

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

### Minimal pgTAP Test Script Setup

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

A basic structure for an SQL test script using pgTAP. It configures output settings, enables error handling, loads pgTAP, declares a test plan, runs simple tests, and concludes the test run.

```sql
-- Disable echo and set format
\unset ECHO
\set QUIET 1
\pset format unaligned
\pset tuples_only true
\pset pager off

-- Enable error handling and rollback on failure
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

-- Load pgTAP
BEGIN;

-- Create extension if needed
CREATE EXTENSION IF NOT EXISTS pgtap;

-- Declare test plan
SELECT plan(5);

-- Run tests
SELECT ok(TRUE, 'First test');
SELECT is(1, 1, 'Second test');

-- Finish and output results
SELECT * FROM finish();

-- Rollback all changes
ROLLBACK;
```

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

### Create pgTAP Extension

Source: https://github.com/theory/pgtap/blob/main/README.md

SQL command to create the pgTAP extension in a PostgreSQL database after installation.

```sql
CREATE EXTENSION pgtap;
```

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

### Run pgTAP Tests with Environment Variables

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Example of running pgTAP's 'make installcheck' command while specifying PostgreSQL connection details via environment variables.

```bash
make installcheck PGUSER=postgres PGDATABASE=testdb
```

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

### pgTAP runtests() Output Example

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Illustrates the typical output format of the runtests() function, including test counts, subtests, and overall results.

```text
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
```

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

### TAP Output Format Example

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Illustrates the standard Test Anything Protocol (TAP) output format, including 'ok' for passed tests, 'not ok' for failed tests, diagnostic comments, and the plan line.

```text
1..3
ok 1 - first test
not ok 2 - second test
#     have: 42
#     want: 43
ok 3 - third test
```

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

### check_test() with expected diagnostics

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

This example shows how to test for expected diagnostic output. Ensure your test function calls `diag()` only after `ok()`.

```sql
SELECT * FROM check_test(
    lc_eq( ''this'', ''THat'' ),
    false,
    'lc_eq() failing test',
    'this is THat',
    E'    Want: this\n    Have: THat'
);
```

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

### Run All Tests with runtests()

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Utilize runtests() to automatically plan, execute, and finish all test functions, including startup, setup, teardown, and shutdown functions. It supports schema and pattern arguments.

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

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

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

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

### pgTAP xUnit Style Test Pattern

Source: https://github.com/theory/pgtap/blob/main/_autodocs/INDEX.md

An example of writing pgTAP tests in an xUnit style using PL/pgSQL functions.

```sql
CREATE FUNCTION test_something() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT ok(condition, 'description');
    RETURN NEXT is(actual, expected, 'description');
END;
$$ LANGUAGE plpgsql;

SELECT * FROM runtests();
```

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

### Upgrade pgTAP Extension

Source: https://github.com/theory/pgtap/blob/main/README.md

SQL command to upgrade an existing unpackaged pgTAP installation to a properly packaged extension.

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

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

### Starting a TODO Block with pgtap

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Initiate a TODO block without specifying the number of tests. This is useful when the exact count of tests to be marked as TODO is not known beforehand.

```sql
SELECT todo_start('Waiting for upstream fix');
SELECT ok(false, 'Will fail gracefully');
SELECT ok(false, 'Will also fail gracefully');
```

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

### Docker Compose Commands for pgTAP

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Commands to build, start, run tests, and shut down a PostgreSQL server with pgTAP using Docker Compose.

```sh
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
```

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

### Example Usage of is()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Demonstrates using is() to compare scalar values, check for specific string matches, and compare entire rows against a composite type.

```sql
SELECT is(ultimate_answer(), 42, 'Meaning of Life');
SELECT is(foo(), 'bar', 'Got bar');
SELECT is(users.*, ROW(1, 'theory', true)::users) FROM users WHERE nick = 'theory';
```

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

### Example: Testing an Interval Column Type

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Shows how to specify an interval type for `col_type_is`, noting that prior to PostgreSQL 17, the format must match PostgreSQL's rendering.

```sql
SELECT col_type_is( 'myschema', 'sometable', 'somecolumn', 'interval second(3)' );
```

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

### Run All pgTAP Tests

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Executes all xUnit-style test functions in the database. Discovers and runs tests in separate transactions, including setup and teardown functions.

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

```plpgsql
CREATE OR REPLACE FUNCTION setup() RETURNS SETOF TEXT AS $$
BEGIN
    INSERT INTO test_data VALUES (1, 'test');
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_something() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT is((SELECT count(*) FROM test_data), 1::bigint);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION teardown() RETURNS SETOF TEXT AS $$
BEGIN
    DELETE FROM test_data;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM runtests();
```

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

### Get PostgreSQL Version String

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Retrieves the full PostgreSQL version string. Useful for logging or conditional logic based on the exact version.

```sql
SELECT pg_version();
```

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

### Example: Testing a Timestamp Column Type

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Demonstrates using `col_type_is` to check a column with a specific timestamp type, including precision.

```sql
SELECT col_type_is( 'myschema', 'sometable', 'somecolumn', 'timestamptz(3)' );
```

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

### pgTAP Test Script for pg_prove

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

A simplified pgTAP test script intended for use with pg_prove, which handles transaction management and setup.

```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();
ROLLబ్యాck;
```

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

### Get pgTAP Version

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Retrieves the installed pgTAP version number. Useful for ensuring compatibility or reporting.

```sql
SELECT pgtap_version();
```

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

### Optional Parameters in has_table

Source: https://github.com/theory/pgtap/blob/main/_autodocs/overview.md

Demonstrates the different valid ways to call the 'has_table' function with optional parameters omitted. These examples show how to call the function with varying levels of specificity for schema, table, and description.

```sql
-- All these are valid
SELECT has_table('schema', 'table', 'description');
SELECT has_table('schema', 'table');
SELECT has_table('table', 'description');
SELECT has_table('table');
```

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

### Test Database Extensions

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Verifies that all extensions within a database or a specific schema match a provided list. Use to ensure the correct extensions are installed and associated.

```sql
SELECT extensions_are( :schema, :extensions, :description );
SELECT extensions_are( :schema, :extensions );
SELECT extensions_are( :extensions, :description );
SELECT extensions_are( :extensions );
```

```sql
SELECT extensions_are(
    'myschema',
    ARRAY[ 'citext', 'isn', 'plpgsql' ]
);
```

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

### runtests()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Executes all xUnit-style test functions defined in the database. This function automatically discovers and runs tests, including setup and teardown logic, within individual transactions.

```APIDOC
## runtests()

### Description
Execute all xUnit-style test functions in the database.

### Returns
- setof text - TAP output lines from executed tests.

### Example
```sql
-- Assuming setup(), test_something(), and teardown() functions are defined
SELECT * FROM runtests();
```
```

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

### Test Prepared Statement Average Performance with performs_within

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-queries.md

This example shows how to use `performs_within` with a prepared statement to check its average performance characteristics over multiple runs. The function runs the query a specified number of times and analyzes the distribution of execution times.

```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 average 250±10ms'
);
```

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

### Run All Tests

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Execute all defined unit test functions. `runtests()` finds and runs test functions within individual transactions, supporting setup and teardown.

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

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

### GitHub Actions Workflow for pgTAP Tests

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

This YAML defines a GitHub Actions workflow to automatically test pgTAP on push or pull request events. It sets up PostgreSQL, installs pgTAP, and runs the tests.

```yaml
name: pgTAP Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:14
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: testdb

    steps:
      - uses: actions/checkout@v2
      - name: Install pgTAP
        run: |
          sudo apt-get install pgxnclient
          pgxn install pgtap
      - name: Run tests
        run: |
          make installcheck PGUSER=postgres
```

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

### Running xUnit Tests

Source: https://github.com/theory/pgtap/blob/main/_autodocs/MANIFEST.txt

Shows how to define and run tests using the xUnit-style testing approach with pgTAP.

```sql
CREATE FUNCTION test_something() RETURNS SETOF TEXT AS $$
  ...
$$;
SELECT * FROM runtests();
```

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

### pgtap_version()

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Retrieves the installed pgTAP version number. This function is helpful for verifying the pgTAP installation and ensuring compatibility.

```APIDOC
## pgtap_version()

### Description
Get the installed pgTAP version.

### Returns
- numeric - pgTAP version number (e.g., `1.3` for version 1.3.x)

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

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

### Script-based Testing Style

Source: https://github.com/theory/pgtap/blob/main/_autodocs/README.md

Demonstrates the script-based testing style in SQL, using SELECT plan() and SELECT ok() for assertions.

```sql
SELECT plan(5);
SELECT ok(condition, 'description');
SELECT * FROM finish();
```

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

### hasnt_extension

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Tests that an extension is NOT installed. It can optionally include a description for the test.

```APIDOC
## hasnt_extension

### Description
Tests that an extension is NOT installed.

### Signature
`hasnt_extension(name, text) → text`
`hasnt_extension(name) → text`

### Parameters
#### Path Parameters
- **extension_name** (name) - Required - Extension name to test
- **description** (text) - Optional - Test description

### Response
#### Success Response (text)
Returns a text string indicating the test result.
```

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

### has_extension

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Tests that an extension is installed. It can optionally include a description for the test.

```APIDOC
## has_extension

### Description
Tests that an extension is installed.

### Signature
`has_extension(name, text) → text`
`has_extension(name) → text`

### Parameters
#### Path Parameters
- **extension_name** (name) - Required - Extension name to test
- **description** (text) - Optional - Test description

### Request Example
```sql
SELECT has_extension('pgtap', 'pgTAP installed');
SELECT has_extension('citext');
```

### Response
#### Success Response (text)
Returns a text string indicating the test result.
```

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

### extensions_are

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Tests that specified extensions are installed. Accepts an array of extension names.

```APIDOC
## extensions_are(name[])

### Description
Tests that specified extensions are installed.

### Method
SQL Function Call

### Parameters
#### Path Parameters
- **name** (text[]) - Required - An array of extension names to check.

### Request Example
```sql
SELECT extensions_are(ARRAY['citext', 'pgtap', 'uuid-ossp']);
```

### Response
#### Success Response (text)
- Returns text indicating the result of the test.
```

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

### Conditional Testing with Skipping and TODOs

Source: https://github.com/theory/pgtap/blob/main/_autodocs/overview.md

Demonstrates how to conditionally execute tests using CASE statements, skip tests based on conditions, and mark tests as TODO. This is useful for handling version-specific features or known issues.

```sql
SELECT plan(5);

-- Skip tests if condition not met
SELECT CASE WHEN version_is_92_or_later THEN
    collect_tap(
        ok(has_feature(), 'Feature works'),
        ok(feature_works(), 'Feature working')
    )
ELSE
    skip('Feature requires PostgreSQL 9.2+', 2)
END;

-- Mark some tests as TODO
SELECT todo('Feature not yet implemented', 2);
SELECT ok(broken_feature(), 'Test 1 (expected to fail)');
SELECT ok(another_broken(), 'Test 2 (expected to fail)');
SELECT todo_end();

SELECT * FROM finish();
```

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

### Get pgTAP Compilation Version

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

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

```pgsql
SELECT pg_version();
```

```pgsql
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://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Execute pgTAP tests in xUnit mode using the `pg_prove` tool.

```bash
# Run xUnit tests
pg_prove -d testdb --runtests
```

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

### Get Operating System Name

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Retrieves the operating system name from when PostgreSQL was built. Useful for environment-specific checks.

```sql
SELECT os_name();
```

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

### hasnt_language(name) → text

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Tests that a specified procedural language is NOT installed on the database. This variant omits the description parameter.

```APIDOC
## hasnt_language(name) → text

### Description
Tests that a procedural language is NOT installed.

### Parameters
#### Path Parameters
- **name** (name) - Required - Language name

### Response
#### Success Response (200)
- **text** (text) - Returns a success message on failure, otherwise returns nothing.
```

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

### Test Prepared Statement Performance with performs_ok

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-queries.md

This snippet demonstrates using `performs_ok` with a prepared statement to verify its execution time against a threshold. Ensure the prepared statement is defined before calling the function.

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

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

### has_language(name) → text

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Tests if a specified procedural language is installed on the database. This variant omits the description parameter.

```APIDOC
## has_language(name) → text

### Description
Tests that a procedural language is installed.

### Parameters
#### Path Parameters
- **name** (name) - Required - Language name

### Response
#### Success Response (200)
- **text** (text) - Returns a success message on failure, otherwise returns nothing.
```

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

### xUnit-style Testing

Source: https://github.com/theory/pgtap/blob/main/_autodocs/README.md

Shows the xUnit-style testing approach in PostgreSQL using a PL/pgSQL function and the runtests() function.

```sql
CREATE FUNCTION test_something() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN NEXT ok(condition, 'description');
END;
$$ LANGUAGE plpgsql;
SELECT * FROM runtests();
```

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

### Configure postgresql.conf for Custom Extension Path

Source: https://github.com/theory/pgtap/blob/main/README.md

Update postgresql.conf to include the custom prefix for extension control and dynamic library paths.

```ini
extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'
```

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

### Basic pgTAP Test Script

Source: https://github.com/theory/pgtap/blob/main/_autodocs/README.md

A basic SQL script demonstrating how to set up a test plan, run assertions, and finish tests using pgtap.

```sql
BEGIN;
CREATE EXTENSION IF NOT EXISTS pgtap;

SELECT plan(3);

-- Test 1: Basic boolean
SELECT ok(1 = 1, 'Math works');

-- Test 2: Equality
SELECT is('foo'::text, 'foo'::text, 'Strings match');

-- Test 3: Schema validation
SELECT has_table('public', 'users', 'Users table exists');

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

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

### todo_start

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Begins a TODO block without specifying the count of tests to be marked as TODO.

```APIDOC
## todo_start

### Description
Begin a TODO block without specifying count.

### Signature
`todo_start(text) → void`
`todo_start() → void`

### Parameters
#### Path Parameters
- **why** (text) - Optional - Reason for TODO status

### Request Example
```sql
SELECT todo_start('Waiting for upstream fix');
SELECT ok(false, 'Will fail gracefully');
SELECT ok(false, 'Will also fail gracefully');
```
```

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

### hasnt_language(name, text) → text

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Tests that a specified procedural language is NOT installed on the database. Includes an optional description for the test.

```APIDOC
## hasnt_language(name, text) → text

### Description
Tests that a procedural language is NOT installed.

### Parameters
#### Path Parameters
- **name** (name) - Required - Language name
- **text** (text) - Required - Test description

### Response
#### Success Response (200)
- **text** (text) - Returns a success message on failure, otherwise returns nothing.
```

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

### has_language(name, text) → text

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Tests if a specified procedural language is installed on the database. Includes an optional description for the test.

```APIDOC
## has_language(name, text) → text

### Description
Tests that a procedural language is installed.

### Parameters
#### Path Parameters
- **name** (name) - Required - Language name
- **text** (text) - Required - Test description

### Request Example
```sql
SELECT has_language('plpgsql', 'PL/pgSQL installed');
```

### Response
#### Success Response (200)
- **text** (text) - Returns a success message on failure, otherwise returns nothing.
```

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

### Load pgTAP in Test Scripts

Source: https://github.com/theory/pgtap/blob/main/_autodocs/configuration.md

Include `pgtap.sql` in your distribution and load it at the beginning of your test scripts. Ensure proper transaction handling and error stopping.

```sql
\i pgtap.sql
```

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

### Test for Inherited Tables

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-misc.md

Asserts that a parent table has child tables defined through inheritance. Useful for verifying table hierarchy setup.

```sql
SELECT has_inherited_tables('parent_table', 'Should have children');
```

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

### Getting Number of Failed Tests with pgtap

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

The `num_failed()` function returns the count of failed tests. This function is only meaningful after tests have been executed.

```sql
SELECT num_failed();
```

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

### Run xUnit Tests with pg_prove

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Command to use pg_prove to run all xUnit test functions through the runtests() function.

```sh
pg_prove -d myapp --runtests
```

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

### Declare Plan and Run Tests Sequentially

Source: https://github.com/theory/pgtap/blob/main/_autodocs/overview.md

Use this script-based testing style to declare the number of tests and run them sequentially. Ensure you call `finish()` at the end.

```sql
SELECT plan(3);
SELECT ok(condition, 'description');
SELECT * FROM finish();
```

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

### Basic Test Script

Source: https://github.com/theory/pgtap/blob/main/_autodocs/overview.md

A fundamental pgtap script demonstrating basic assertions, value comparisons, and schema validation. Ensure the pgtap extension is created and a plan is set before running tests.

```sql
BEGIN;
CREATE EXTENSION pgtap;

SELECT plan(3);

-- Test 1: Basic assertion
SELECT ok(1 = 1, 'One equals one');

-- Test 2: Value comparison
SELECT is(COUNT(*)::integer, 3, 'Table has 3 rows') FROM test_table;

-- Test 3: Schema validation
SELECT has_table('users', 'Users table exists');

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

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

### Test Extension Non-Installation

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-schema.md

Use `hasnt_extension` to verify that a specific PostgreSQL extension is not installed. This is useful for ensuring a clean environment or testing dependency requirements.

```sql
SELECT hasnt_extension('unneeded_extension', 'Should not have unneeded_extension');
SELECT hasnt_extension('another_unneeded_extension');
```

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

### Basic ok() Usage

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Use ok() to assert a boolean condition. The optional description helps identify the test on failure. It handles NULL results as failures.

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

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

### Testing foreign keys in temporary tables

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Shows how to use `fk_ok()` to test foreign key constraints involving temporary tables. It utilizes `pg_my_temp_schema()` or equivalent to correctly reference the temporary schema.

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

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

### Unconditional Pass Assertion

Source: https://github.com/theory/pgtap/blob/main/_autodocs/api-reference-assertions.md

Unconditionally passes a test, useful for marking setup steps as successful. Equivalent to `ok(TRUE, description)`. Use sparingly.

```sql
SELECT pass('Test description');
```

```sql
SELECT pass('Setup completed successfully');
```

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

### Create a Batched TAP Test Function

Source: https://github.com/theory/pgtap/blob/main/doc/pgtap.md

Define a function that RETURNS SETOF TEXT to batch multiple TAP tests using RETURN NEXT. This allows running several tests within a single function.

```sql
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;
```

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

### pgTAP Complete Test Script Pattern

Source: https://github.com/theory/pgtap/blob/main/_autodocs/INDEX.md

A common pattern for writing a complete pgTAP test script, including setup, test execution, and teardown.

```sql
BEGIN;
CREATE EXTENSION IF NOT EXISTS pgtap;

SELECT plan(N);  -- N = number of tests

-- Run tests
SELECT ok(condition, 'description');
SELECT is(actual, expected, 'description');
SELECT has_table('table_name');

-- Finish
SELECT * FROM finish();
ROLLBACK;
```