pg_mockable

Overview

Version

Build

You can build the DEB packages for pg_mockable using pig build:

pig build pkg pg_mockable         # build DEB packages

Install

You can install pg_mockable directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_mockable;          # Install for current active PG version

pig ext install -y pg_mockable -v 18  # PG 18
pig ext install -y pg_mockable -v 17  # PG 17
pig ext install -y pg_mockable -v 16  # PG 16
pig ext install -y pg_mockable -v 15  # PG 15
pig ext install -y pg_mockable -v 14  # PG 14

dnf install -y pg_mockable_18       # PG 18
dnf install -y pg_mockable_17       # PG 17
dnf install -y pg_mockable_16       # PG 16
dnf install -y pg_mockable_15       # PG 15
dnf install -y pg_mockable_14       # PG 14

apt install -y postgresql-18-pg-mockable   # PG 18
apt install -y postgresql-17-pg-mockable   # PG 17
apt install -y postgresql-16-pg-mockable   # PG 16
apt install -y postgresql-15-pg-mockable   # PG 15
apt install -y postgresql-14-pg-mockable   # PG 14

Create Extension:

CREATE EXTENSION pg_mockable;

Usage

Sources: pg_mockable upstream README, v1.1.0 tag, PGXN pg_mockable, local source tarball pg_mockable-1.1.0.tar.gz.

pg_mockable creates mockable wrapper functions for PostgreSQL routines. It is mainly useful in database tests where application code should call a stable wrapper, while tests temporarily replace the wrapper’s return value.

CREATE EXTENSION pg_mockable CASCADE;

The extension installs into the fixed mockable schema and is not relocatable.

Mock Built-In Time Functions

mockable.now() is pre-created because mocking now() also covers the related current-time wrappers exposed by this extension.

SELECT mockable.now();

SELECT mockable.mock(
  'pg_catalog.now()',
  '2026-06-17 10:00:00+08'::timestamptz
);

SELECT mockable.now();
SELECT mockable.current_timestamp();
SELECT mockable.current_date();

CALL mockable.unmock('pg_catalog.now()');

mockable.mock(regprocedure, anyelement) stores the mock value and returns it. mockable.unmock(regprocedure) clears the mock and restores the wrapper to call the original routine.

Wrap Application Functions

Use mockable.wrap_function() to create a thin wrapper in the mockable schema:

CREATE SCHEMA app;

CREATE FUNCTION app.answer()
RETURNS int
LANGUAGE sql
RETURN 42;

SELECT mockable.wrap_function('app.answer()');

SELECT mockable.answer();                 -- 42
SELECT mockable.mock('app.answer()', 7);   -- 7
SELECT mockable.answer();                 -- 7

CALL mockable.unmock('app.answer()');
SELECT mockable.answer();                 -- 42

The first argument is a regprocedure, so include argument types when the function is overloaded:

SELECT mockable.wrap_function('pg_catalog.current_setting(text, boolean)');

If automatic wrapper generation is not sufficient, pass the exact CREATE OR REPLACE FUNCTION statement as the second argument:

SELECT mockable.wrap_function(
  'app.answer()',
  $$
  CREATE OR REPLACE FUNCTION mockable.answer()
  RETURNS int
  LANGUAGE sql
  RETURN app.answer();
  $$
);

Version 1.1.0 also adds optional debug logging for wrapped/mockable routines through raise_debug_messages$ on mockable.wrap_function(...) and the mock_memory.raise_debug_messages column.

Mock Lifetime

The default mock lifetime is transaction-scoped. For values that must survive dump/restore or later transactions, create the wrapper with a persistent lifetime:

SELECT mockable.wrap_function(
  'app.answer()',
  mock_duration$ => 'PERSISTENT'
);

Persistent mocks should be explicitly cleared when the test fixture no longer needs them:

CALL mockable.unmock('app.answer()');

Search Path Caveat

Application code must actually call the wrapper, for example mockable.now() or mockable.answer(), for the mock to apply. Some PL/pgSQL code can be redirected by adjusting search_path, but expressions such as table defaults are compiled to function OIDs; adding mockable to search_path later does not rewrite those references. Prefer explicit mockable.* calls in code that is meant to be testable.

Caveats

• Version 1.1.0 supports PostgreSQL 14-18. It is a SQL extension and does not need shared_preload_libraries.
• pg_mockable owns the mockable schema; installing it in another schema is not supported by the control file.
• Wrapper privileges are derived from the wrapped routine. The tests verify that wrapping a private function does not grant execute privilege to roles that could not call the original function.