I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.

But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.

I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.

For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.

For quite some time now (first version that I see is from 2008-06-17 – 2 years ago), we can use pgTAP.

What it is? First, let's start with name. pg is obviously from PostgreSQL. TAP is acronym and means ‘Test Anything Protocol'. In case you're not familiar with it – basically it is just a standardized way of reporting success/failure of test program(s).

So. First of all – should you use it?

Answer is complex, but generally – if you use any stored procedures/functions, and or triggers – I would at least consider using them.

Let's assume you have trigger which changes full name of person to properly capitalized version. That is – it will change ‘hubert LUBACZEWSKI' into ‘Hubert Lubaczewski'.

First – let's get pgTap. On it's site I go to download page, and download newest tar.bz2, unpacked, and did make && make install (you might need to use sudo make install in case Pg is installed somewhere where you don't have write access to).

After installation I got 5 new files:

/home/pgdba/work/share/postgresql/contrib/pgtap.sql

/home/pgdba/work/share/postgresql/contribuninstall_pgtap.sql

/home/pgdba/work/share/doc/postgresql/contrib/README.pgtap

/home/pgdba/work/bin/pg_prove

/home/pgdba/work/bin/pg_tapgen

(of course in another situation, the paths might be different).

Now. For to use pgtap, we need to load it's functions to database. At this moment quick notice – I could have used make TAPSCHEMA=something, but for this simple test, I just don't care.

Nice. This test only shows that I can insert data and the trigger doesn't break. Now, let's add tests for actual capitalization. These can be in the same file (t-01.sql), but I'll add them in 2nd test file:

As you can see the test clearly failed, it showed there it failed (tests/t-03.sql, not ok 1 – failed test #1), and it showed nicely what it got, and what it should get to pass.

Now, I can fix the trigger, make sure the test pass, and release the code.

The great thing is that if I'll ever want to switch from (for example) plpgsql trigger to plperl one – I got cover that checks whatever errors we previously had so we will not hit the same issue twice. And that's a great thing.

One final note: regardless of how big your test suite is, how many thousands of cases it checks, how many edge cases, how many previous errors – it can never be said: “my code is correct because tests pass".

It can be said: “my code is not correct because tests fail", or “I don't know of any more bugs in the code, because all tests have passed".

But tests will never prove that your code is fully correct, because it is technically not possible to test all of the possible cases.

With this in mind – use tests. Play with it, and see what functionality David put in there (the is() and lives_ok() functions are just the beginning). It is really astonishing piece of work. And will make your life, as DBA simpler. After some getting used to it 🙂

@Tasos:
No idea. I don’t even have windows to be able to test it. I don’t actually think it shouldn’t be possible – it’s just some c code, so assuming you can get some kind of c compiler, it should work, i guess.