Testing stored functions using pgTAP

I Recently posted article with a "skeleton" schema that can be used to create its schemas of PostgreSQL.
In addition to scripts deployment diagrams, object creation, there were examples of stored functions and Unit tests for them.



In this article I want for example pg_skeleton to elaborate on how to write tests for stored functions in PostgreSQL using the pgTAP.

The pgTAP tests, as the name implies, the output given the text in plain text format TAP (Test Anything Protocol). This format is accepted by many CI systems. We use Jenkins TAP Plugin.

When you install the extension in database create a stored function (by default in the public schema), which we will use when writing tests. A large part of the functions of the various assertions. The full list can be found here: http://pgtap.org/documentation.html

Test will function from the example schema test_user:
First installed pg_skeleton. (If you want to write tests in your diagram from the installation instructions pg_skeleton follow only the part about pgtap and load the extension in the database)

The tests I have tried to make it similar to those used in real projects, and to use more different f-iy pgTAP.
Before run 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:
the

select plan(7+2+1);

In this case, 7 is the number of tests run from a file user_crud.sql (tests f-iy), 2 — the number of tests in the file user_schema.sql 1 — single-line test (checking the cover function tests) directly in the file run_user.sql.
The documentation pgTAP considered in the main tests, called a separate select queries — suitable for testing the schema validation or simple f-rd, has no side effects (such tests in user_schema.sql).
But when testing complex scenarios when you need to call a few f-rd, and next is passed the result of the previous, you can combine tests in a stored function, which will execute a script containing several tests. An example of such a function in the file test/functions_user.sql.
The function need to be declared as returning the set of strings:
the

create or replace function test.test_user_0010()
returns setof text as $$
--A room in the name of f-s is optional, but may be useful to run the test f th
--in a certain order when running tests with runtests().

--Declare a variable which will store the ID of the added record:
declare
v_user_id integer;
begin
- First test - check that the feature works without exceptions:
return next lives_ok('select test_user.add_user("testuser unique"::varchar);',
'test_user.add_user doesnt throw exception');
--Add another record, save the ID, verify it is correct (>0):
v_user_id := test_user.add_user('blah blah');
return next cmp_ok(v_user_id,
'>',
0,
'test_user.add_user: returns 'ok');
--Check that the record really is.
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');
--Function change the user should return the user ID:
return next is(test_user.alter_user(v_user_id,'new user name blah'),
v_user_id,
'test_user.alter_user: returns 'ok');
--Check that a record has really changed:
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 record updates');
- The function of deleting a user should return its id:
return next is(test_user.delete_user(v_user_id),
v_user_id,
'test_user.delete_user: returns 'ok');
- The last test. Check that the user is really deleted:
return next is_empty('select 1 from test_user.users where user_id=' || v_user_id::varchar,
'test_user.delete_user: deletes ok');

$$ language plpgsql;

To run the tests directly from sql:
the

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

in this case we get clean TAP output:
the

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 pg_prove utility:
the

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

Then the output will be more cilvekiem:
the

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 variables for host, port, user name, and dB for you to substitute script /test/run_tests.sh

I hope that now all who have code in stored functions of PostgreSQL will be unit tests!

Article based on information from habrahabr.ru