In a previous article of mine I’ve been bitten by the “temporary function issue” (which isn’t an issue at all, of course).
I needed a way to use the
now() function in different ways to do a sort of “time travel” over a history table.
I’ve found a few easy™ ways to accomplish the same task and have now distilled one that seems to me to be the “best one“™. I will make use of a temporary table.
The trick is that a new function, called
mynow() will access a table without an explicit schema qualifier but relying on a controlled
search_path. This approach opens the door to a controlled table masking thanks to the temporary schema each session has. Let’s see this function first.
create or replace function mynow( out ts timestamp ) language plpgsql as $l0$ begin select * into ts from mytime; if not found then ts := now(); end if; end; $l0$;
If you put a timestamp in the table
mytime, then that timestamp will be used a the current time. If there’s nothing in there, then the normal function output will be used.
First of all, you need to create an always-empty non-temporary table like this in the public schema:
create table mytime ( ts timestamp );
So the function will start working soon with the default time line. If I inserted a time stamp straight in there, I’d set the function behavior for all sessions at once. But this isn’t normally the objective.
As soon as a user needs a different reference time for its time travelling, she needs to do the following:
set search_path = pg_temp,"$user", public; -- 1st create table mytime ( like public.mytime including all ); -- 2nd insert into mytime values ( '2000-01-01' ); -- 3rd
That’s it. More or less.
The first statement alters the
search_path setting so the
pg_temp schema becomes the first one to be searched in and, thus, the “default” schema.
The second one creates a temporary table (re-read the previous sentence, please!) “just like the public one“. Please note the schema qualifying used with the
The third one will insert into that temporary table (you re-read it, didn’t you?) a value to be used as the reference time in your time traveling.
Time to test, now. Without closing the current PostgreSQL connection try the following:
select * from mynow(); ts --------------------- 2000-01-01 00:00:00 (1 row)
If you close the connection and re-open it, the
pg_temp schema will vanish and the very same query will behave “just like the plain old”
The same behavior can be accomplished without closing the connection by either deleting from the temporary table or by resetting the
search_path to it normal value, thus “unmasking” the persistent table and resetting the function behavior;
If you go back to my article, you can replace the function call
mynow() and enable a full blown table time travelling.
Of course, there are also alternative implementations and even those with more care in enforcing more controlled behaviors. Feel free to propose your very own.