(or, how to use History Tables in PostgreSQL)
If you would like to store somewhere the full history of all the insertions,
updates and deletions in all your tables (to maintain a wiki-like database),
there is no simple way to do it... but
here you can find a
way to do it, in an article written by Thomas Liske.
It works in PostgreSQL, creating a PL/Perl database trigger called log_history()
which adds a line to a new table called history.<SCHEMA>_<TABLENAME> with
a timestamp (hist_ts), the operation (hist_op) and all
the columns, whenever a INSERT, UPDATE or DELETE is executed. This effectively
stores all the history of the tables where the trigger is used.
Here you can view the log_history
trigger, and a simple function to bind the trigger on all the tables in a
schema, history_create_triggers.
You can call this function for schema "public" with SELECT history_create_triggers('public');
These two functions are written in PL/Perl and PL/pgSQL, respectively, so
you have to add those language to your PostgreSQL database, if you have
not already done so, with CREATE LANGUAGE plperl; and
CREATE LANGUAGE plpgsql;
