Temporary functions (kind of) without schema qualifiers

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$
  select * into ts from mytime;
  if not found then
    ts := now();
  end if;

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 like predicate.

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();
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 oldnow() function.

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 now() with 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.


7 thoughts on “Temporary functions (kind of) without schema qualifiers”

  1. I’ve been using Postgres for almost 10 years, and didn’t know about pg_temp. Is this special schema documented anywhere? I can’t find it in the online docs, and would like to know more about it. Specifically, are pg_temp tables stored on disk or in memory? If they are stored in memory, then perhaps we could use them to run our application tests more quickly.


    1. Hi. I could find details about pg_temp in the documentation at “19.11.1. Statement Behavior”.
      It’s a temporary schema, one for each session that’s destroyed at the end. It’s in memory (RAM) not on permanent storage.
      Of course you can use them for whatever purpose you want, but forget about them if you need different sessions to access the same temporary object. There are many more things in the manual than stars in the sky! 😉


  2. Nice approach, the only drawback I see is that your function is not temporary, that means it is not destroyed once the session ends. So far, it sounds much like a configuration driver function than a temporary one.
    Also, I would not change the search_path (dangerous) but test if the temporary table exists via pg_table_is_visible or something alike within the function itself, something like the following:

    select c.relname, pg_table_is_visible( c.oid ), n.nspname
    from pg_class c join pg_namespace n on n.oid = c.relnamespace
    where n.nspname like ‘pg_temp%’
    and c.relkind = ‘r’ and c.relname = ‘mytime’;


    1. My function is to replace now() which isn’t temporary at all. It’s a persistent function accessing a temporary table.
      This is why I added “(sort of)” in the title.
      I do often change the search_path at runtime in order to easily get different “data sets”. This is what the schemas are for too.
      Finally, I need to call that function in a query or view, thus testing for its existence at runtime seems to me to be a burden.
      Nonetheless I’ll take some time to think about your points. Thanks.


  3. Just curious, but if you are already addicted to search_path and schema changes, why not overriding the same function in different schemas and call it thru the very first entry in the search_path?


  4. In my history table implementation (maybe you can give that a look) I want to have a single view that can work for both “current” data (by somehow using the now() function) and for “past” or “future” data (by using something different).
    What you suggest is interesting and valuable but requires two different views in two different schemas.
    In that case I wouldn’t bother to define two now() functions in two schemas, but just two slightly different views.
    Moreover, by using the pg_temp as the underlying mechanism I can have different reference times for different sessions, all at once over the very same single view.

    Finally, I need to admit I am not sure about the meaning of “addiction to search_path and schema changes”.

    Thanks for the feedbacks.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s