Contents

• • pg_readme_version: 0.6.1
• pg_mockable – mock PostgreSQL functions
  • Installation
  • Usage
  • Object reference
    • Schema: mockable
    • Tables
    • Routines
    • Types
  • Colophon

---

pg_extension_name: pg_mockable pg_extension_version: 0.3.0 pg_readme_generated_at: 2023-04-03 17:32:47.479952+01

pg_readme_version: 0.6.1

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:

sql create extension pg_mockable cascade;

You can install the extension into a different schema, but choose your schema name wisely, since pg_mockable is not relocatable.

Usage

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

sql call mockable.wrap_function('pg_catalog.now()`);

This call will bring into being: mockable.now(), which just does a return pg_catalog.now().

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

sql call 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 won't work, because mockable.now() always 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.

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 shorted 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 6 attributes:

1. 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)

2. mock_memory.return_type text

   • NOT NULL

3. mock_memory.unmock_statement text

   • NOT NULL

4. mock_memory.is_prewrapped_by_pg_mockable boolean

   • DEFAULT false

5. mock_memory.mock_value text

6. mock_memory.mock_duration text

   • DEFAULT 'TRANSACTION'::text
   • CHECK (mock_duration = ANY (ARRAY['TRANSACTION'::text, 'PERSISTENT'::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:

sql 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

```sql 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

```sql 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() = pgcatalog.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;

<<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;

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

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

Colophon

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