postgres pgTAP unit test pg_prove - ghdrako/doc_snipets GitHub Wiki

unit tests are coded in SQL too.

The relation-testing functions for implementing unit tests based on result sets. From the documentation, let’s pick a couple examples, Testing against static result sets as VALUES:

SELECT results_eq( 
 'SELECT * FROM active_users()', 
 $$ 
 VALUES (42, 'Anna'), 
        (19, 'Strongrrl'), 
        (39, 'Theory') 
 $$, 
 'active_users() should return active users' 
);

and ARRAYS:

SELECT results_eq( 
 'SELECT * FROM active_user_ids()', 
  ARRAY[ 2, 3, 4, 5] 
);

Straight from the pg_prove command-line tool for running and harnessing pg-TAP tests, we can see how it looks:

1 % pg_prove -U postgres tests/ 
 tests/coltap.....ok 
 tests/hastap.....ok 
 tests/moretap....ok 
 tests/pg73.......ok 
 tests/pktap......ok 
 All tests successful.
 Files=5, Tests=216, 1 wallclock secs 9 ( 0.06 usr 0.02 sys + 0.08 cusr 0.07 csys = 0.23 CPU)
 Result: PASS You

integration with ci/cd

https://manpages.ubuntu.com/manpages/trusty/man1/pg_virtualenv.1.html

pg_virtualenv is a tool that creates a temporary PostgreSQL installation that will exist only while you’re running your tests.

Regression Tests

The RegreSQL tool implements that idea. It ??nds SQL ??les in your code repos-itory and allows registering plan tests against them, and then it compares the results with what’s expected.

Instrumentation sql

PostgreSQL implements the application_name parameter, which you can set in the connection string and with the SET command within your session. IT is then possible to have it reported in the server’s logs, and it’s also part of the system activity view pg_stat_activity.