File Release Notes and Changelog

Release Name: 0.02

Release Notes

pgTAP 0.02

pgTAP is a collection of TAP-emitting unit testing functions written in
PL/pgSQL and PL/SQL. It is based on the Test::More module distributed with
Perl 5. You could even consider it a port of sorts.


For the impatient, to install pgTAP into a PostgreSQL database, just do this:

    make test
    make install
    make installcheck

If you encounter an error such as:

  "Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as

    gmake test
    gmake install
    gmake installcheck

If you encounter an error such as:

  make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a
package management system such as RPM to install PostgreSQL, be sure that the
-devel package is also installed. If necessary, add the path to pg_config to
your `$PATH` environment variable:

  env PATH=$PATH:/path/to/pgsql/bin make && make test && make install

And finally, if all that fails, copy the entire distribution directory to the
`contrib/` subdirectory of the PostgreSQL source code and try it there.

If you want to schema-qualify pgTAP (that is, install all of its functions
into their own schema), set the `$TAPSCHEMA` environment variable to the name
of the schema you'd like, for example:

    make TAPSCHEMA=tap

The test target uses the included pg_prove script to do its testing. It
supports a number of environment variables that you might need to use,
including all the usual PostgreSQL client environment varibles:


Once pgTAP habe been built and tested, you can install it into a database:

    psql dbname -f pgtap.sql

If you want pgTAP to be available to all new databases, install it into the
template1 dtabase:

    psql template1 -f pgtap.sql

If you want to remove pgTAP from a database, run the drop_pgtap.sql script:

    psql dbname -f drop_pgtap.sql

Both scripts will also be installed in the "contrib" directory under the
directory output by `pg_config --sharedir`. So you can always do this,
as well:

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

But do be aware that, if you've specified a schema using `$TAPSCHEMA`, it will
always be created and the functions placed in it.

Running pgTAP Tests

You can also distribute pgtap.sql with any PostgreSQL distribution, such as a
custom data type. For such a case, if your users want to run your test suite,
just be sure to start a transaction, load the functions in your test.sql file,
and then rollback the transaction at the end of the script. Here's an example:

    -- Load the TAP functions.
    \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();

Of course, if you already have the pgTAP functions in your testing database,
you should skip `\i pgtap.sql` at the beginning of the script.

Now you're ready to run your test script!

    % psql -d try -AtP pager= -v ON_ERROR_ROLLBACK=1 -f test.sql
    ok 1 - My test passed, w00t!

You'll need to have all of those switches there to ensure that the output is
proper TAP and that all changes are reversed -- including the loading of the
test functions -- in the event of an uncaught exception. Or you can specify
them in the test file, like so:

    \pset format unaligned
    \pset tuples_only
    \pset pager

Or save yourself some effort -- and run a batch of tests scripts at once -- by
using pg_prove:

    % ./pg_prove -d try test_*.sql
    All tests successful.
    Files=1, Tests=1,  0 wallclock secs ( 0.03 usr  0.00 sys +  0.02 cusr  0.01 csys =  0.06 CPU)
    Result: PASS

Yep, that's all there is to it.


The purpose of pgTAP is to provide a wide range of testing utilities that
output TAP. TAP, or the "Test Anything Protocol", is an emerging standard for
representing the output from unit tests. It owes its success to its format as
a simple text-based interface that allows for practical machine parsing and
high legibility for humans. TAP started life as part of the test harness for
Perl but now has implementations in C/C++, Python, PHP, JavaScript, Perl, and
now PostgreSQL.

I love it when a plan comes together

Before anything else, you need a testing plan. This basically declares how
many tests your script is going to run to protect against premature failure.

The preferred way to do this is to declare a plan by calling the plan() function:

    SELECT plan( 42 );

There are rare cases when you will not know beforehand how many tests your
script is going to run. In this case, you can declare that you have no plan.
(Try to avoid using this as it weakens your test.)

  SELECT * FROM no_plan();

Often, though, you'll be able to calculate the number of tests, like so:

    SELECT plan( COUNT(*) )
      FROM foo;

At the end of your script, you should always tell pgTAP that the tests have
completed, so that it can output any diagnostics about failures or a
discrepancy between the planned number of tests and the number actually run:

    SELECT * FROM finish ( );

Test names

By convention, each test is assigned a number in order. This is largely done
automatically for you. However, it's often very useful to assign a name to
each test. Would you rather see this?

      ok 4
      not ok 5
      ok 6

Or this?

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

The later gives you some idea of what failed. It also makes it easier to find
the test in your script, simply search for "simple exponential".

All test functions take a name argument. It's optional, but highly suggested
that you use it.

I'm ok, you're not ok

The basic purpose of pgTAP -- and of any TAP-emitting test framework, for that
matter -- is to print out either "ok #" or "not ok #", depending on whether a
given test succeeded or failed. Everything else is just gravy.

All of the following functions return "ok" or "not ok" depending on whether
the test succeeded or failed.

### ok ( boolean, text ) ###
### ok ( boolean ) ###

    SELECT ok( :this = :that, :test_name );
This function simply evaluates any expression `:this = :that` is just a
simple example) and uses that to determine if the test succeeded or
failed. A true expression passes, a false one fails. Very simple.

For example:

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

(Mnemonic:  "This is ok.")

:test_name is a very short description of the test that will be printed
out. It makes it very easy to find a test in your script when it fails and
gives others an idea of your intentions. :test_name is optional, but we
*very* strongly encourage its use.

Should an ok() fail, it will produce some diagnostics:

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

### is (anyelement, anyelement, text) ###
### is (anyelement, anyelement ) ###
### isnt (anyelement, anyelement, text) ###
### isnt (anyelement, anyelement ) ###

    SELECT is  ( :this, :that, $test_name );
    SELECT isnt( :this, :that, $test_name );

Similar to ok(), is() and isnt() compare their two arguments with `=` and
`<>` respectively and use the result of that to determine if the test
succeeded or failed. So these:

    -- Is the ultimate answer 42?
    SELECT is( ultimate_answer(), 42, 'Meaning of Life' );

    -- foo() doesn't return empty
    SELECT isnt( foo(), '', 'Got some foo' );

are similar to these:

    SELECT ok(   ultimate_answer() =  42, 'Meaning of Life' );
    SELECT isnt( foo()             <> '', 'Got some foo'    );

(Mnemonic: "This is that." "This isn't that.")

So why use these? They produce better diagnostics on failure. ok() cannot
know what you are testing for (beyond the name), but is() and isnt() know
what the test was and why it failed. For example this test:

    \set foo '\'' waffle '\''
    \set bar '\'' yarblokos '\''
    SELECT is( :foo::text, :bar::text,   'Is foo the same as bar?' );

Will produce something like this:

    # Failed test 17:  "Is foo the same as bar?"
    #          got: waffle
    #     expected: yarblokos

So you can figure out what went wrong without re-running the test.

You are encouraged to use is() and isnt() over ok() where possible,
however do not be tempted to use them to find out if something is true or

    -- XXX BAD!
    SELECT is( is_valid(9), TRUE, 'A tree grows in Brooklyn' );

This does not check if `is_valid(9)` is true, it checks if it *returns*
TRUE. Very different. Similar caveats exist for FALSE. In these cases, use

    SELECT ok( is_valid(9),       'A tree grows in Brooklyn' );

### matches ( anyelement, text, text ) ###
### matches ( anyelement, text ) ###

    SELECT matches( :this, '^that', :test_name );

Similar to ok(), matches() matches :this against the regex `/^that/`.

So this:

    SELECT matches( :this, '^that', 'this is like that' );

is similar to:

    SELECT ok( :this ~ '^that', 'this is like that' );

(Mnemonic "This matches that".)

Its advantages over ok() are similar to that of is() and isnt(): Better
diagnostics on failure.

### imatches ( anyelement, text, text ) ###
### imatches ( anyelement, text ) ###

    SELECT imatches( :this, '^that', :test_name );

These are just like matches() except that the regular expression is

### doesnt_match ( anyelement, text, text ) ###
### doesnt_match ( anyelement, text ) ###
### doesnt_imatch ( anyelement, text, text ) ###
### doesnt_imatch ( anyelement, text ) ###

    SELECT doesnt_match( :this, '^that', :test_name );

These functions work exactly as matches() and imataches() do, only they
check if :this *does not* match the given pattern.

### alike ( anyelement, text, text ) ###
### alike ( anyelement, text ) ###
### ialike ( anyelement, text, text ) ###
### ialike ( anyelement, text ) ###

    SELECT alike( :this, 'that%', :test_name );

Similar to ok(), alike() matches :this against the SQL LIKE pattern
'that%'. ialike() matches case-insensitively.

So this:

    SELECT ialike( :this, 'that%', 'this is alike that' );

is similar to:

    SELECT ok( :this ILIKE 'that%', 'this is like that' );

(Mnemonic "This is like that".)

Its advantages over ok() are similar to that of is() and isnt(): Better
diagnostics on failure.

### unalike ( anyelement, text, text ) ###
### unalike ( anyelement, text ) ###
### unialike ( anyelement, text, text ) ###
### unialike ( anyelement, text ) ###

    SELECT unalike( :this, 'that%', :test_name );

Works exactly as alike(), only it checks if :this *does not* match the
given pattern.

### pass ( text ) ###
### pass ( ) ###
### fail ( text ) ###
### fail ( ) ###

    SELECT pass( :test_name );
    SELECT fail( :test_name );

Sometimes you just want to say that the tests have passed. Usually the
case is you've got some complicated condition that is difficult to wedge
into an ok(). In this case, you can simply use pass() (to declare the test
ok) or fail() (for not ok). They are synonyms for ok(1) and ok(0).

Use these functions very, very, very sparingly.

### throws_ok ( text, char(5), text ) ###
### throws_ok ( text, char(5) ) ###
### throws_ok ( text ) ###

    SELECT throws_ok(
        'INSERT INTO try (id) VALUES (1)',
        'We should get a unique violation for a duplicate PK'

When you want to make sure that an exception is thrown by PostgreSQL under
certain circumstances, use throws_ok() to test for those circumstances.

The first argument should be a string representing the query to be
executed. throws_ok() will use the PL/pgSQL `EXECUTE` statement to execute
it and catch any exception.

The second argument should be an exception error code. If this value is
not NULL, throws_ok() will check the thrown exception to ensure that it is
the expected exception. For a complete list of error codes, see [Appendix A.]( in the [PostgreSQL documentation](

The third argument is of course a brief test name.

Should a throws_ok() test fail it produces appropriate diagnostic
messages. For example:

    not ok 81 - simple error
    # Failed test "simple error"
    #       caught: 23505: duplicate key value violates unique constraint "try_pkey"
    #     expected: 23506

Idea borrowed from the Test::Exception Perl module.

### lives_ok ( text, text ) ###
### lives_ok ( text ) ###

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

The inverse of throws_ok(), these functions test to ensure that a SQL
statement does *not* throw an exception. Should a lives_ok() test faile,
it produces appropriate diagnostic messages. For example:

    not ok 85 - simple success
    # Failed test "simple success"
    #         died: 23505: duplicate key value violates unique constraint "try_pkey"

Idea borrowed from the Test::Exception Perl module.


If you pick the right test function, you'll usually get a good idea of what
went wrong when it failed. But sometimes it doesn't work out that way. So here
we have ways for you to write your own diagnostic messages which are safer
than just \echo.

### diag ( text ) ###

Returns a diagnostic message which is guaranteed not to interfere with
test output. Handy for this sort of thing:

    -- Output a diagnostic message if the collation is not en_US.UTF-8.
    SELECT diag(
         E'These tests expect LC_COLLATE to be en_US.UTF-8,\n'
      || 'but yours is set to ' || setting || E'.\n'
      || 'As a result, some tests may fail. YMMV.'
      FROM pg_settings
     WHERE name = 'lc_collate'
       AND setting <> 'en_US.UTF-8';

Which would produce:

    # These tests expect LC_COLLATE to be en_US.UTF-8,
    # but yours is set to en_US.UTF-8.
    # As a result, some tests may fail. YMMV.

To Do

* Update the Makefile to process pg_prove and set the proper path to Perl on
  the shebang line. Will likely require a `$(PERL)` environment variable.
* Update Makefile to require TAP::Harness.
* Add a test function to test test functions and update the test script to
  use it.

Suported Versions

pgTAP has been tested on the following builds of PostgreSQL:

* PostgreSQL 8.3.1 on i386-apple-darwin9.2.2

If you know of others, please submit them! Use
`psql template1 -c 'SELECT VERSION()'` to get the formal build version and OS.

David E. Wheeler <>


* Michael Schwern and chromatic for Test::More.
* Adrian Howard for Test::Exception.

Copyright and License

Copyright (c) 2008 Kineticode, Inc. Some rights reserved.

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement is
hereby granted, provided that the above copyright notice and this paragraph
and the following two paragraphs appear in all copies.



Change Log

0.02  2008-06-17T16:26:41
      - Converted the documentation to Markdown.
      - Added pg_prove, a Perl script that use TAP::Harness to run tests and
        report the results, juse like the Perl program `prove`.
      - Fixed `no_plan()` so that it no longer emits a plan, which apparently
        was wrong. Now `finish()` outputs it when there's no plan.
      - Fixed the test script so that it now emits a proper plan.
      - Removed all of the configuration settings from `pgtap.sql`, as they're
        now handled by `pg_prove`. I've mengioned them in the README for
      - Added `lives_ok()`.
      - Moved the creation of temporary tables into `plan()`, so that
        everything is properly self-contained.
      - Improved the handling of transactions. Test scripts are now assumed to
        be single transactions with a ROLLBACK at the end. This makes it so
        that test scripts don't have to include `drop_pgtap.sql`.
      - Updated `pg_prove` to rollback on an error, rather than just stop.
        This allows all test functions to be properly rolled back, too, in a
        test script that includes them but then encounters an unhandled
      - Updated `pg_prove` to emit an appropriate error message if no test
        scripts are specified.
      - Added a Makefile. It uses the typical PostgreSQL installation
        procedures to install pgTAP. The SQL files have now been turned into
        `.in` templates that are processed by `make`.
      - Added support for schema qualification of test functions. Just set the
        `$TAPSCHEMA` environment variable when running `make`.
      - Added support for standard PostgreSQL-type regression testing by just
        copying the test script, setting some variables inside it, and
        providing an `expected/` directory. The test now lives in the `sql/`
      - Changed all instances of `RETURN QUERY SELECT` to `RETURN NEXT`, which
        should allow pgtap to run on versions of PostgreSQL earlier than 8.3.
        Thanks to Niel Conway for the suggestion.
Powered By FusionForge