How to test PROCEDURE in PostgreSQL with pgTAP?

Question

Is there a best practice for unit testing a PostgreSQL 11+ PROCEDURE (NOT a FUNCTION) using pgTap.

For example, how would one recommend unit testing a stored procedure like this:

CREATE OR REPLACE PROCEDURE foo.do_something(IN i_value INT)
AS
$$
BEGIN
  PERFORM foo.call_function_1(i_value);
  COMMIT;      
  PERFORM foo.call_function_2(i_value);
  COMMIT;      
  CALL foo.another_procedure(i_value);
END;
$$
LANGUAGE plpgsql;

This becomes difficult since pgTap unit tests run via a stored function like this:

SELECT * FROM runtests('foo'::NAME);

This executes in a transaction, making it impossible to execute stored procedures that modify transaction state by calling COMMIT or ROLLBACK.

I posted my own answer, but I'm still open to other ideas if anyone has any. — akagixxer, Dec 15 '18 at 01:01

akagixxer · Accepted Answer · 2018-12-17T19:09:02.817

Here is an approach I came up with inspired by using interfaces along with mocking frameworks in other languages.

First we move the COMMIT operation to a stored procedure like this:

CREATE PROCEDURE foo.do_commit()
AS
$$
BEGIN
  COMMIT;
END;
$$
LANGUAGE plpgsql;

Then we alter the actual stored procedure to call do_commit instead of using COMMIT command directly. For example:

CREATE OR REPLACE PROCEDURE foo.do_something(IN i_value INT)
AS
$$
BEGIN
  PERFORM foo.call_function_1(i_value);
  CALL foo.do_commit();
  CALL foo.another_procedure(i_value);
END;
$$
LANGUAGE plpgsql;

Since the unit tests are executed in a transaction that gets rolled back, we can replace the do_commit call temporarily to something mocked out for testing. A test could look something like this:

CREATE FUNCTION test.test_do_something()
RETURNS SETOF TEXT 
AS 
$$
BEGIN
  CREATE TEMPORARY TABLE commit_calls
  (
    commit_call BOOLEAN NOT NULL DEFAULT TRUE
  )
  ON COMMIT DROP;

  CREATE TEMPORARY TABLE function_calls
  (
    the_value INT NOT NULL
  )
  ON COMMIT DROP;

  CREATE OR REPLACE PROCEDURE foo.do_commit()
  AS
  $mock_do_commit$
  BEGIN
    INSERT INTO commit_calls (commit_call)
    VALUES (DEFAULT);
  END;
  $mock_do_commit$
  LANGUAGE plpgsql;

  CREATE OR REPLACE FUNCTION foo.call_function_1(i_value INT)
  RETURNS VOID
  AS
  $mock_call_function_1$
    INSERT INTO function_calls (the_value)
    VALUES (i_value);
  $mock_call_function_1$
  LANGUAGE sql;

  -- EXECUTE
  CALL foo.do_something(9);
  CALL foo.do_something(100);

  -- VERIFY
  RETURN NEXT assert.is((SELECT COUNT(*) FROM commit_calls)::INT, 2, 'verify transaction commits');
  RETURN NEXT assert.bag_eq(
    'SELECT the_value FROM function_calls',
    'VALUES (9), (100)',
    'verify function call values');
END;
$$
LANGUAGE plpgsql;

The idea is to temporarily mock out actual function calls for testing.
This way one can unit test a stored procedure without committing real transactions.
When the test ends it rolls back the transaction and the all of the changes are discarded.

How to test PROCEDURE in PostgreSQL with pgTAP?

1 Answers1