Skip to content

Instantly share code, notes, and snippets.

@thehesiod
Created May 20, 2020 09:41
Show Gist options
  • Save thehesiod/d0314d599c721216f075375c667e2d9a to your computer and use it in GitHub Desktop.
Save thehesiod/d0314d599c721216f075375c667e2d9a to your computer and use it in GitHub Desktop.
fake/mock now on postgres DB
-- inspiration from https://dba.stackexchange.com/questions/69988/how-can-i-fake-inet-client-addr-for-unit-tests-in-postgresql/70009#70009
CREATE SCHEMA if not exists override;
create table if not exists override.freeze_time_param_type
(
param_type text not null primary key
);
insert into override.freeze_time_param_type values ('enabled'), ('timestamp'), ('tick') on conflict do nothing;
create table if not exists override.freeze_time_params (
param text not null primary key references override.freeze_time_param_type(param_type),
value jsonb
);
insert into override.freeze_time_params values
('enabled', 'false'),
('timestamp', 'null'),
('tick', 'false')
on conflict(param) do nothing; -- don't overwrite existing state
CREATE OR REPLACE FUNCTION override.freeze_time(freeze_time timestamp with time zone, tick bool default false)
RETURNS void AS
$$
BEGIN
insert into override.freeze_time_params(param, value) values
('enabled', 'true'),
('timestamp', EXTRACT(EPOCH FROM freeze_time)::text::jsonb),
('tick', tick::text::jsonb)
on conflict(param) do update set
value = excluded.value;
END
$$ language plpgsql;
create OR REPLACE function override.unfreeze_time()
RETURNS void AS
$$
BEGIN
insert into override.freeze_time_params(param, value) values
('enabled', 'false')
on conflict(param) do update set
value = excluded.value;
END
$$ language plpgsql;
CREATE OR REPLACE FUNCTION override.now()
RETURNS timestamptz AS
$$
DECLARE enabled text;
DECLARE tick text;
DECLARE timestamp timestamp;
BEGIN
select into enabled value from override.freeze_time_params where param = 'enabled';
select into tick value from override.freeze_time_params where param = 'tick';
if enabled then
select into timestamp to_timestamp(value::text::decimal) from override.freeze_time_params where param = 'timestamp';
if tick then
timestamp = timestamp + '1 second'::interval;
update override.freeze_time_params set value = extract(epoch from timestamp)::text::jsonb where param = 'timestamp';
end if;
return timestamp;
else
return pg_catalog.now();
end if;
END
$$ language plpgsql;
-- NOTE: we cannot create functions that take no parameters like CURRENT_DATE so your sql should always use now()
-- NOTE: the search_path order is: https://www.postgresonline.com/article_pfriendly/279.html
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment