See if there's a practical way to offer query functions

[mreithub]

A nice syntax would be:

SELECT * FROM recall_at('my_table', now() - interval '3 months') WHERE ...;

But the function would have to be implemented in a way that fully facilitates table indexes.

[mreithub]

So far the only thing I've come up with (apart from letting users query the _log table explicitly) is creating separate _at() functions for each table dynamically.

[mreithub]

One other method would be to create/replace a temporary view. For a table foo, this might look like:

# SELECT recall_at('foo', now() - interval '3 months');
NOTICE: created temporary view foo_past
# SELECT * FROM foo_past;
# SELECT * FROM foo_past WHERE id = 15;
# ...

Internally this could look like:

CREATE OR REPLACE TEMPORARY VIEW foo_past AS
SELECT a,b,c,d FROM foo_log WHERE _log_start <= :ts AND (_log_end IS NULL OR _log_end > :ts)

a,b,c,d being all the columns of foo (so everything except _log_start and _log_end)

Advantages:

• the query planner will run every time you query the view, allowing it to optimize based on what you're actually querying for (i.e. the WHERE clause).
• the temporary view will be deleted at the end of the session (and is session-local, so different sessions could call the function independent from each other)
• relatively neat

Disadvantage:

• two step process

Other convenience query functions could be added (e.g. to show the history for a single record)

[mreithub]

implemented in 1a0e3ec, released in v0.9.2