Overwrite Postgres now() function-CodePudding

In my test database, I want to override now() in Postgres, so I can travel to a certain point in time. I'd like to override it like this:

CREATE SCHEMA if not exists override;
CREATE OR REPLACE FUNCTION override.now()
RETURNS timestamp with time zone
AS
$$
BEGIN
RETURN pg_catalog.now()   COALESCE(
  NULLIF(current_setting('timecop.offset_in_seconds', true), '')::integer, 0
) * interval '1 second';
END;
$$
LANGUAGE plpgsql STABLE PARALLEL SAFE STRICT;
SET search_path TO DEFAULT;
SELECT set_config('search_path', 'override,' || current_setting('search_path'), false);

To enable it, I call

SET timecop.offset_in_seconds = 3600 -- 1 hour ahead

To disable it, I call

RESET timecop.offset_in_seconds

The problem is, that Postgres somehow doesn't use the function:

app_test=# select now();
              now
-------------------------------
 2022-12-04 10:22:26.824469 00
(1 row)

app_test=# SET timecop.offset_in_seconds = 3600;
SET
app_test=# select now();
              now
-------------------------------
 2022-12-04 10:22:34.481502 00
(1 row)

Looking at the now() method itself, I seems like the search path searches in pg_catalog before the override schema:

app_test=# \df  now
                                                                                        List of functions
   Schema   | Name |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code |       Description
------------ ------ -------------------------- --------------------- ------ ------------ ---------- ---------- ---------- ------------------- ---------- ------------- --------------------------
 pg_catalog | now  | timestamp with time zone |                     | func | stable     | safe     | postgres | invoker  |                   | internal | now         | current transaction time

So, how could I move my overwritten now() BEFORE the pg_catalog?

CodePudding user response：

pg_catalog is always on the search path, but you can opt not to have it in the beginning:

SET search_path = override, pg_catalog;