`pg_mockable` – mock PostgreSQL functions

pg_extension_name	pg_extension_version	pg_readme_generated_at	pg_readme_version
pg_mockable	1.0.1	2024-01-17 12:04:08 +0000	0.6.5

`pg_mockable` – mock PostgreSQL functions

The pg_mockable PostgreSQL extension can be used to create mockable versions of functions from other schemas.

Installation

To make the extension files available to PostgreSQL:

make install

To make the extension available in the current database:

create extension pg_mockable cascade;

Usage

First, use mockable.wrap_function() to create a very thin function wrapper for whichever function you wish to wrap:

select mockable.wrap_function('pg_catalog.now()`);

This call will bring into being: mockable.now(), which just does a return pg_catalog.now(). In other words: the wrapper function, when not mocking, calls the original function.

If, for some reason, this fails, you can specify the precise CREATE OR REPLACE FUNCTION statement as the second argument to wrap_function():

select mockable.wrap_function('pg_catalog.now', $$
create or replace function mockable.now()
    returns timestamptz
    stable
    language sql
    return pg_catalog.now();
$$);

(In fact, this example is a bit contrived; mockable.now() always pre-exists, because the need to mock now() was the whole reason that this extension was created in the first place. And now() is a special case, because, to mock now() effectively, a whole bunch of other current date-time retrieval functions have a mockable counterpart that all call the same mockable.now() function, so that mocking pg_catalog.now() also effectively mocks current_timestamp(), etc.)

After mocking a function, you can use it as you would the original function.

`search_path` and the `mockable` schema

Note, that, in some circumstances, you can use the search_path to altogether bypass the mockable schema (and thus the mock (wrapper) functions therein). But, this is only in contexts which are compiled at run-time, such as PL/pgSQL function bodies. A DEFAULT expression for a table or view column, for example, will be compiled down to references to the actual function objects involved, thus making it impossible to do a post-hoc imposition of the mockable schema by prepending ti to the search_path.

Of course, defaults are only that—defaults—and you could, for instance, override them while running tests, but that seems altogether more cumbersome than directly referencing, for instance, DEFAULT mockable.now(). There remains the argument of development-time dependencies versus run-time dependencies, of course, and the fact that the latter should be kept to a minimum…

Speaking of PostgreSQL search_paths, this is a good opportunity to plug a very detailed writeup the extension author did in 2022: https://blog.bigsmoke.us/2022/11/11/postgresql-schema-search_path

Object reference

Schema: `mockable`

pg_mockable must be installed in the mockable schema. Hence, it is not relocatable.

The mockable schema belongs to the pg_mockable extension.

Postgres (as of Pg 15) doesn't allow one to specify a default schema, and do something like schema = 'mockable' combined with relocatable = true in the .control file. Therefore I decided to choose the mockable schema name for you, even though you might have very well preferred something shorter like mock, even shorter like mck, or more verbose such as mock_objects.

Tables

There are 1 tables that directly belong to the pg_mockable extension.

Table: `mock_memory`

The mock_memory table has 8 attributes:

mock_memory.routine_signature regprocedure

The mockable routine oid (via its regprocedure alias).

Check the official Postgres docs for more information about regprocedure and other OID types.

As evidenced by the test_dump_restore__pg_mockable() procedure, storing an regprocedure is not a problem with pg_dump/pg_restore. The same is true for other oid alias types, because these are all serialized as their text representation during pg_dump and then loaded from that text representation again during pg_restore. See https://dba.stackexchange.com/a/324899/79909 for details.
- NOT NULL
- PRIMARY KEY (routine_signature)
mock_memory.mock_signature text

The mock (wrapper) function its calling signature.

The mock_signature, contrary to routine_signature, is stored as text, because we want to be able to set in the BEFORE trigger before the function is actually created in the AFTER trigger.
- NOT NULL
- UNIQUE (mock_signature)
mock_memory.return_type text
- NOT NULL
mock_memory.unmock_statement text
- NOT NULL
mock_memory.mock_value text
mock_memory.mock_duration text
- DEFAULT 'TRANSACTION'::text
- CHECK (mock_duration = ANY (ARRAY['TRANSACTION'::text, 'PERSISTENT'::text]))
mock_memory.pg_extension_name name
mock_memory.pg_extension_version text

Routines

Function: `"current_date"()`

current_date() is derived from mockable.now(). To mock it, mock pg_catalog.now().

Function return type: date

Function attributes: STABLE

Function: `"current_time"()`

current_time() is derived from mockable.now(). To mock it, mock pg_catalog.now().

Unlike its standard (PostgreSQL) counterpart, current_time() does not support a precision parameter. Feel free to implement it.

Function return type: time with time zone

Function attributes: STABLE

Function: `"current_timestamp"()`

current_timestamp() is derived from mockable.now(). To mock it, mock pg_catalog.now().

Unlike its standard (PostgreSQL) counterpart, current_timestamp() does not support a precision parameter. Feel free to implement it.

Function return type: timestamp with time zone

Function attributes: STABLE

Function: `"localtime"()`

localtime() is derived from mockable.now(). To mock it, mock pg_catalog.now().

Unlike its standard (PostgreSQL) counterpart, localtime() does not support a precision parameter. Feel free to implement it.

Function return type: time without time zone

Function attributes: STABLE

Function: `"localtimestamp"()`

localtimestamp() is derived from mockable.now(). To mock it, mock pg_catalog.now().

Unlike its standard (PostgreSQL) counterpart, localtimestamp() does not support a precision parameter. Feel free to implement it.

Function return type: timestamp without time zone

Function attributes: STABLE

Function: `mockable.now()`

Mockable wrapper function for now().

Function return type: timestamp with time zone

Function attributes: STABLE, RETURNS NULL ON NULL INPUT

Function: `mockable.timeofday()`

Function return type: text

Function attributes: STABLE

Function-local settings:

SET DateStyle TO Postgres

Function: `mockable.transaction_timestamp()`

Function return type: timestamp with time zone

Function attributes: STABLE

Function: `mock_memory__after_magic()`

Function return type: trigger

Function-local settings:

SET search_path TO pg_catalog

Function: `mock_memory__before_magic()`

Function return type: trigger

Function-local settings:

SET search_path TO pg_catalog

Function: `mock_memory__reset_value()`

This trigger ensures that the mocked value is always forgotten before transaction end.

Resetting the value in turn ensures that another trigger unmocks the wrapper function; that is, it will be restored to act as a thin wrapper around the original (wrapped) function.

Function return type: trigger

Function-local settings:

SET search_path TO pg_catalog

Function: `mock (regprocedure, anyelement)`

Function arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`	`routine_signature$`	`regprocedure`
`$2`	`INOUT`	`mock_value$`	`anyelement`

Function return type: anyelement

Function-local settings:

SET search_path TO pg_catalog

Function: `pg_mockable_meta_pgxn()`

Returns the JSON meta data that has to go into the META.json file needed for PGXN—PostgreSQL Extension Network packages.

The Makefile includes a recipe to allow the developer to: make META.json to refresh the meta file with the function's current output, including the default_version.

pg_mockable can indeed be found on PGXN: https://pgxn.org/dist/pg_mockable/

Function return type: jsonb

Function attributes: STABLE

Function: `pg_mockable_readme()`

Generates the text for a README.md in Markdown format with the help of the pg_readme extension.

This function temporarily installs pg_readme if it is not already installed in the current database.

Function return type: text

Function-local settings:

SET search_path TO mockable, pg_temp
SET pg_readme.include_view_definitions_like TO true
SET pg_readme.include_routine_definitions_like TO {test__%}

Function: `pg_proc (regprocedure)`

Conveniently go from function calling signature description or OID (regprocedure) to pg_catalog.pg_proc.

Example:

SELECT pg_proc('pg_catalog.current_setting(text, bool)');

Function arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`		`regprocedure`

Function return type: pg_proc

Function attributes: STABLE

Procedure: `test_dump_restore__pg_mockable (text)`

This procedure is to be called by the test_dump_restore.sh and test_dump_restore.sql companion scripts, once before pg_dump (with test_stage$ = 'pre-dump' argument) and once after pg_restore (with the test_stage$ = 'post-restore').

Procedure arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`	`test_stage$`	`text`

Procedure-local settings:

SET search_path TO pg_catalog, mockable
SET plpgsql.check_asserts TO true
SET pg_readme.include_this_routine_definition TO true

CREATE OR REPLACE PROCEDURE mockable.test_dump_restore__pg_mockable(IN "test_stage$" text)
 LANGUAGE plpgsql
 SET search_path TO 'pg_catalog', 'mockable'
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
declare
begin
    assert test_stage$ in ('pre-dump', 'post-restore');

    if test_stage$ = 'pre-dump' then
        create schema test__schema;
        create function test__schema.func() returns int return 8;
        perform wrap_function('test__schema.func()');
        assert mockable.mock('test__schema.func()', 88::int) = 88::int;
        assert mockable.func() = 88;

        create function test__schema.func2() returns text[] return array['beh', 'blah'];
        perform wrap_function('test__schema.func2()', mock_duration$ => 'PERSISTENT');
        assert mockable.func2() = array['beh', 'blah'];
        assert mockable.mock('test__schema.func2()', array['boe', 'bah']) = array['boe', 'bah'];
        assert mockable.func2() = array['boe', 'bah'];

        assert mockable.mock('pg_catalog.now()', '2022-01-02 10:30'::timestamptz)
            = '2022-01-02 10:30'::timestamptz;
        assert mockable.now() = '2022-01-02 10:30'::timestamptz;

    elsif test_stage$ = 'post-restore' then
        assert exists (select from mock_memory where routine_signature = 'now()'::regprocedure);
        assert mockable.now() = pg_catalog.now(),
            'This wrapper function should have been restored to a wrapper of the original function.';

        assert exists (select from mock_memory where routine_signature = 'test__schema.func()'::regprocedure);
        assert mockable.func() = 8,
            'The wrapper function should have been restored to a wrapper of the original function.';

        assert exists (select from mock_memory where routine_signature = 'test__schema.func2()'::regprocedure);
        assert mockable.func2() = array['boe', 'bah'],
            'The wrapper function should have been restored, and not unmocked.';
        call mockable.unmock('test__schema.func2()');
        assert mockable.func2() = array['beh', 'blah'];
    end if;
end;
$procedure$

Procedure: `test__pg_mockable()`

Procedure-local settings:

SET search_path TO pg_catalog
SET plpgsql.check_asserts TO true
SET pg_readme.include_this_routine_definition TO true

CREATE OR REPLACE PROCEDURE mockable.test__pg_mockable()
 LANGUAGE plpgsql
 SET search_path TO 'pg_catalog'
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
declare
    _now timestamptz;
begin
    assert mockable.now() = pg_catalog.now();
    assert mockable.current_date() = current_date;

    assert mockable.mock('pg_catalog.now()', '2022-01-02 10:20'::timestamptz)
        = '2022-01-02 10:20'::timestamptz;
    perform mockable.mock('pg_catalog.now()', '2022-01-02 10:30'::timestamptz);

    assert mockable.now() = '2022-01-02 10:30'::timestamptz,
        'Failed to mock `pg_catalog.now()` as `mockable.now()`.';
    assert mockable.current_date() = '2022-01-02'::date;
    assert mockable.localtime() = '10:30'::time;

    call mockable.unmock('pg_catalog.now()');
    assert pg_catalog.now() = mockable.now();
    assert current_date = mockable.current_date();

    create schema test__schema;
    create function test__schema.func() returns int return 8;
    perform mockable.wrap_function('test__schema.func()');

    --
    -- Now, let's demonstrate how to use the `search_path` to alltogether skip the mocking layer…
    --

    _now := now();  -- just to not have to use qualified names

    perform mockable.mock('pg_catalog.now()', '2022-01-02 10:20'::timestamptz);

    perform set_config('search_path', 'pg_catalog', true);
    assert now() = _now;

    perform set_config('search_path', 'mockable, pg_catalog', true);
    assert now() = '2022-01-02 10:20'::timestamptz;

    <<test_that_grants_are_copied>>
    begin
        create role underling;

        create function test__schema.private_func() returns int return 100;
        revoke execute on function test__schema.private_func() from public;
        assert not has_function_privilege('underling', 'test__schema.private_func()', 'EXECUTE');

        perform mockable.wrap_function('test__schema.private_func()');
        assert not has_function_privilege('underling', 'mockable.private_func()', 'EXECUTE');
        perform mockable.mock('test__schema.private_func()', 1000::int);
        assert not has_function_privilege('underling', 'mockable.private_func()', 'EXECUTE');

        grant execute on function test__schema.private_func() to underling;
        assert has_function_privilege('underling', 'test__schema.private_func()', 'EXECUTE');

        perform mockable.mock('test__schema.private_func()', 1000::int);
        assert has_function_privilege('underling', 'mockable.private_func()', 'EXECUTE');

    end test_that_grants_are_copied;

    <<recursive_mock_attempt>>
    begin
        assert current_schema = 'mockable';
        assert 'now()'::regprocedure = 'mockable.now()'::regprocedure;
        assert 'now()'::regprocedure != 'pg_catalog.now()'::regprocedure;

        perform mockable.mock('now()', '2021-01-01 00:00'::timestamptz);

        raise assert_failure using
            message = 'Mocking an unwrapped function should have been forbidden.';
    exception
        when no_data_found then  -- Good.
    end recursive_mock_attempt;

    <<recursive_wrap_attempt>>
    begin
        assert current_schema = 'mockable';
        assert 'now()'::regprocedure = 'mockable.now()'::regprocedure;
        assert 'now()'::regprocedure != 'pg_catalog.now()'::regprocedure;

        perform mockable.wrap_function('now()');

        raise assert_failure using
            message = 'Wrapping a wrapper function should have been forbidden.';
    exception
        when invalid_recursion then  -- Good.
    end recursive_wrap_attempt;

    create extension pg_mockable_dependent_test_extension
        with version 'constver';

    raise transaction_rollback;
exception
    when transaction_rollback then
end;
$procedure$

Procedure: `unmock (regprocedure)`

Procedure arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`	`routine_signature$`	`regprocedure`

Procedure-local settings:

SET search_path TO pg_catalog

Function: `wrap_function (regprocedure, mock_memory_duration)`

Function arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`	`function_signature$`	`regprocedure`
`$2`	`IN`	`mock_duration$`	`mock_memory_duration`	`'TRANSACTION'::mock_memory_duration`

Function return type: mock_memory

Function: `wrap_function (regprocedure, text, mock_memory_duration)`

Function arguments:

Arg. #	Arg. mode	Argument name	Argument type	Default expression
`$1`	`IN`	`function_signature$`	`regprocedure`
`$2`	`IN`	`create_function_statement$`	`text`
`$3`	`IN`	`mock_duration$`	`mock_memory_duration`	`'TRANSACTION'::mock_memory_duration`

Function return type: mock_memory

Types

The following extra types have been defined besides the implicit composite types of the tables and views in this extension.

Enum type: `mock_memory_duration`

CREATE TYPE mock_memory_duration AS ENUM (
    'TRANSACTION',
    'SESSION',
    'PERSISTENT'
);

Authors and contributors

Rowan originated this extension in 2022 while developing the PostgreSQL backend for the FlashMQ SaaS MQTT cloud broker. Rowan does not like to see himself as a tech person or a tech writer, but, much to his chagrin, he is. Some of his chagrin about his disdain for the IT industry he poured into a book: Why Programming Still Sucks. Much more than a “tech bro”, he identifies as a garden gnome, fairy and ork rolled into one, and his passion is really to regreen and reenchant his environment. One of his proudest achievements is to be the third generation ecological gardener to grow the wild garden around his beautiful family holiday home in the forest of Norg, Drenthe, the Netherlands (available for rent!).

Colophon

This README.md for the pg_mockable extension was automatically generated using the pg_readme PostgreSQL extension.

Name		Name	Last commit message	Last commit date
Latest commit History 20 Commits
bin		bin
expected		expected
pg_mockable_dependent_test_extension		pg_mockable_dependent_test_extension
sql		sql
.gitignore		.gitignore
LICENCE.txt		LICENCE.txt
META.json		META.json
Makefile		Makefile
README.md		README.md
pg_mockable.control		pg_mockable.control

License

bigsmoke/pg_mockable

Folders and files

Latest commit

History

Repository files navigation

pg_mockable – mock PostgreSQL functions

Installation

Usage

search_path and the mockable schema

Object reference

Schema: mockable

Tables

Table: mock_memory

Routines

Function: "current_date"()

Function: "current_time"()

Function: "current_timestamp"()

Function: "localtime"()

Function: "localtimestamp"()

Function: mockable.now()

Function: mockable.timeofday()

Function: mockable.transaction_timestamp()

Function: mock_memory__after_magic()

Function: mock_memory__before_magic()

Function: mock_memory__reset_value()

Function: mock (regprocedure, anyelement)

Function: pg_mockable_meta_pgxn()

Function: pg_mockable_readme()

Function: pg_proc (regprocedure)

Procedure: test_dump_restore__pg_mockable (text)

Procedure: test__pg_mockable()

Procedure: unmock (regprocedure)

Function: wrap_function (regprocedure, mock_memory_duration)

Function: wrap_function (regprocedure, text, mock_memory_duration)

Types

Enum type: mock_memory_duration

Authors and contributors

Colophon

About

Topics

Resources

License

Stars

Watchers

Forks

Languages

`pg_mockable` – mock PostgreSQL functions

`search_path` and the `mockable` schema

Schema: `mockable`

Table: `mock_memory`

Function: `"current_date"()`

Function: `"current_time"()`

Function: `"current_timestamp"()`

Function: `"localtime"()`

Function: `"localtimestamp"()`

Function: `mockable.now()`

Function: `mockable.timeofday()`

Function: `mockable.transaction_timestamp()`

Function: `mock_memory__after_magic()`

Function: `mock_memory__before_magic()`

Function: `mock_memory__reset_value()`

Function: `mock (regprocedure, anyelement)`

Function: `pg_mockable_meta_pgxn()`

Function: `pg_mockable_readme()`

Function: `pg_proc (regprocedure)`

Procedure: `test_dump_restore__pg_mockable (text)`

Procedure: `test__pg_mockable()`

Procedure: `unmock (regprocedure)`

Function: `wrap_function (regprocedure, mock_memory_duration)`

Function: `wrap_function (regprocedure, text, mock_memory_duration)`

Enum type: `mock_memory_duration`