Testing Stored Functions with pgTAP

Recently, I posted an article with the "skeleton" of a data schema that you can use to create your PostgreSQL schemas.
In addition to the actual deployment of the schema, creation of objects, there were examples of stored functions and unit tests for them.



In this article, I would like to take a closer look at the example of pg_skeleton on how to write tests for PostgreSQL stored functions using pgTAP .

The pgTAP tests, as the name implies, output text in plain text TAP (Test Anything Protocol) format. This format is adopted by many CI systems. We use Jenkins TAP Plugin .
')
When installing the extension, the database creates stored functions (by default in the public scheme), which we will use when writing tests. Most of the functions are various assertions. The full list can be found here: http://pgtap.org/documentation.html

We will test the functions from the sample test_user scheme:
First, install pg_skeleton . (If you want to write tests at once in your scheme - from the installation instructions for pg_skeleton, follow only the part about pgtap and load the extension into the database)

I tried to make the tests similar to those used in real projects, and use more different f-th pgTAP.
Before starting the tests, you must specify their number by calling the function plan (int).
In our example, this call is in the file test / tests / run_user.sql:

select plan(7+2+1); 

In this case, 7 is the number of tests run from the user_crud.sql file (tests f-th), 2 is the number of tests in the user_schema.sql file, 1 is a one-line test (checking the coverage of functions with tests) directly in the run_user.sql file.
The pgTAP documentation deals mainly with tests triggered by individual select requests — this is suitable for testing the schema, or for testing simple functions that have no side effects (such tests in user_schema.sql).
But when testing complex scenarios, when you need to call several functions, and the result of the previous one is transferred to the next one, you can combine tests into a stored function that will execute a script containing several tests. An example of such a function in the test / functions_user.sql file.
The function must be declared as returning a set of lines:

 create or replace function test.test_user_0010() returns setof text as $$ --   - ,        - --        runtests(). -- ,       : declare v_user_id integer; begin --  - ,    ,   : return next lives_ok('select test_user.add_user(''testuser unique''::varchar);', 'test_user.add_user doesnt throw exception'); --   ,   , ,    (>0): v_user_id := test_user.add_user('blah blah'); return next cmp_ok(v_user_id, '>', 0, 'test_user.add_user: returns ok'); --,    . return next results_eq('select user_name::varchar from test_user.users where user_id=' || v_user_id::varchar, 'select ''blah blah''::varchar', 'test_user.add_user inserts ok'); --      : return next is(test_user.alter_user(v_user_id,'new user name blah'), v_user_id, 'test_user.alter_user: returns ok'); --,    : return next results_eq('select user_name::varchar from test_user.users where user_id=' || v_user_id::varchar, 'select ''new user name blah''::varchar', 'test_user.alter_user updates record'); --      id: return next is(test_user.delete_user(v_user_id), v_user_id, 'test_user.delete_user: returns ok'); -- . ,    : return next is_empty('select 1 from test_user.users where user_id=' || v_user_id::varchar, 'test_user.delete_user: deletes ok'); end; $$ language plpgsql; 

You can run tests directly from sql:

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/run_user.sql 

in this case, we get a clean tap at the output:

 plan | 1..10
 test_user_0010 | ok 1 - test_user.add_user doesnt throw exception
 test_user_0010 | ok 2 - test_user.add_user: returns ok
 test_user_0010 | ok 3 - test_user.add_user inserts record
 test_user_0010 | ok 4 - test_user.alter_user: returns ok
 test_user_0010 | ok 5 - test_user.alter_user updates record
 test_user_0010 | ok 6 - test_user.delete_user: returns ok
 test_user_0010 | ok 7 - test_user.delete_user: deletes ok
 tables_are | ok 8 - Schema test_user contains users table
 columns_are | ok 9 - test_user.users column check
 test_scheme_check_func | ok 10 - all functions in schema test_user are covered with tests.

Or using the pg_prove utility:

 pg_prove -h $db_host -p $db_port -d $db_name -U $db_user tests/run_*.sql 

Then the output will be more readable:

 tests / run_user.sql .. ok     
 All tests successful.
 Files = 1, Tests = 10, 0 wallclock secs (0.04 usr + 0.00 sys = 0.04 CPU)
 Result: PASS

In pg_skeleton, the variables for host, port, username and database will be replaced by the /test/run_tests.sh script for you

I hope that now everyone who has code in the PostgreSQL stored functions will have unit tests!