pgTAP ~ Unit Tests for PostgreSQL ~ Part II ~

as promised in my previous post regarding pgTAP ( aka Part I ) here, I’ll continue this topic with checking some data in the database. Setup and basic handling of pgTAP was described in Part I also, for this reason I’ll concentrate on the test functions, which are used in the following script frame (just replace “##put_ypur_tests_here##” with the test scenarios described later on):

-- Format the output for nice TAP.
\pset format unaligned
\pset tuples_only true
\pset pager
-- Revert all changes on failure.
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true
\set QUIET 1
BEGIN;
\i ./pgtap.sql
-- create two tables with referential constraint
create table tab1 (id integer not null, a_text varchar(200), dt timestamp default now(), CONSTRAINT tab1_pkey PRIMARY KEY (id));
create table tab2 (id integer not null, a_text varchar(200), id_ref integer, CONSTRAINT id_ref FOREIGN KEY (id_ref) REFERENCES tab1 (id));
-- insert some test data
insert into tab1 (id,a_text) values (1,'test entry one');
insert into tab1 (id,a_text) values (2,'test entry two');
insert into tab1 (id,a_text) values (3,'test entry three');
insert into tab2 (id,a_text,id_ref) values (1,'ref test entry one',1);
insert into tab2 (id,a_text,id_ref) values (2,'ref test entry one',2);
insert into tab2 (id,a_text,id_ref) values (3,'ref test entry one',3);
SELECT plan(10); -- run a maximum of ten tests
##put_your_tests_here##
-- get the results
SELECT * FROM finish();
ROLLBACK;

What do we want to test ?

• Are the inserted values really there ?
  The preferrable way to check data is via prepared statements compared to your expected result/array of results, if you just want to test plain data.
  A very convenient way is the function results_eq(), easy to show with an example: 

  -- check inserted data
-- ...with two prepared statements. 2nd example will be more flexible with cursors
PREPARE ids_fetched AS select id from tab1 where id in (1,2,3) order by id asc;
PREPARE ids_expected AS VALUES (1),(2),(3);
SELECT results_eq( 'ids_fetched', 'ids_expected', 'fetched the expected ids from tab1');
PREPARE ids_fetched1 AS select id from tab1 where id in (1,2,3) order by id asc;
PREPARE ids_fetched2 AS select id from tab2 where id in (1,2,3) order by id asc;
SELECT results_eq( 'ids_fetched1', 'ids_fetched2');

  You don’t have to put your expected values in a prepared statement (like in the 1st example), it is also possible to pass this values as function parameters for set_eq():

  -- ...with a mixture of prepared statements and predefined values
PREPARE text_fetched AS select a_text::text from tab1 where id in (1,2) order by id asc;
SELECT set_eq( 'text_fetched', ARRAY[ 'test entry one','test entry two' ], 'fetched the expected text from tab1');


• Does a function return the expected result ?
  In addition to test the availability of expected values is to check the return values of your own designed functions. Let’s assume that we created a function which returns all the ids from tab1 in ascending order, now let’s test if it is working as expected. In this case there are two alternatives performing the test recordset by recordset. On the one hand by simple function/statement comparison, on the other hand by using cursors. I’ll show you both:

  
-- create a (simple) function
create or replace function get_ids_tab1() returns setof integer as 'select id from tab1 order by id asc' language 'sql';
-- check output of function
-- ...with simple calls
SELECT results_eq('select * from get_ids_tab1()', 'select id from tab1 order by id asc','get_ids_tab1() should return ids from relation tab1 in ascending order');
-- ...with cursors
DECLARE ids_function CURSOR FOR SELECT * FROM get_ids_tab1();
DECLARE ids_relation CURSOR FOR SELECT id FROM tab1 order by id asc;
SELECT results_eq('ids_function'::refcursor,'ids_relation'::refcursor,'Gotcha ! function returned the expected values.');


• Raising errors intentionally and catching them

  -- simulate some errors
PREPARE throw_error AS insert into tab1 (id,a_text) values (1,'this insert throws a duplicate key error');
SELECT throws_ok('throw_error','23505',NULL,'Insert failed because of duplicate key violation (id)');


  But how do one know this error number postgres will throw ?
  throws_ok() will tell you . Have a look at the next example. It will try to insert a row in tab2 with a reference to id “4″ in tab1, which obviously doesn’t exist. Therefore we expect an error. Since we don’t know the error number, I put the value “-1″ as error number and the function call will fail with a verbose message what was thrown, and what we did expect:

  
PREPARE ref_fail AS insert into tab2 (id, a_text, id_ref) values (4,'ref test entry four',4);
SELECT throws_ok('ref_fail','-1',NULL,'Insert failed because of (ref_id)');


  Running the test script will produce the following output (in my case this was the 6th test which had been executed):

  
# Failed test 6: "Insert failed because of (ref_id)"
# caught: 23503: insert or update on table "tab2" violates foreign key constraint "id_ref"
# wanted: -1


  Aaaahhhh….the violated foreign key constraint has the errorcode 23503, so we can replace the “-1″ by “23503″ and the test will succeed.

These are just some of the available test functions pgTAP provides. A complete overview is here and it is worth looking.

Hopefully you got a first, short overview what pgTAP is for, please let me know if you miss something or there’s something wrong.

Advertisement