Currently, the way we do this on LedgerSMB is to have SQL scripts which insert records into a test results table. This table is created in a transaction which runs the tests, and the tests always rollback. A wrapper program in Perl runs these scripts, checks the results of the test_result table, and reports errors back up to the test harness. There are some advantages here, including the fact that tests can be run on a live, production DB without contaminating it (and thus also move between QA and support as a tool), and we don't require any additional components to run the tests. In fact, you can run the tests passably using just psql.
One possible direction we are considering going is to move the test scripts to pgTAP and also centralize test data logic. This would seem to give us more options and might allow us to do exception testing and expand the reach of the test scripts, but it would mean additional dependencies for testing, and would likely rule out as a general support tool or one that could be run as part of the installation.
Which approach is best? The current one or pgTap? Or would it be better to adopt a hybrid approach?