2014

JanFebMarApr
MayJunJulAug
SepOctNovDec

2013

JanFebMarApr
MayJunJulAug
SepOctNovDec

more...

2011

JanFebMarApr
MayJunJulAug
SepOctNovDec

2010

JanFebMarApr
MayJunJulAug
SepOctNovDec

2009

JanFebMarApr
MayJunJulAug
SepOctNovDec

2008

JanFebMarApr
MayJunJulAug
SepOctNovDec

2007

JanFebMarApr
MayJunJulAug
SepOctNovDec

2006

JanFebMarApr
MayJunJulAug
SepOctNovDec

2005

JanFebMarApr
MayJunJulAug
SepOctNovDec

2004

JanFebMarApr
MayJunJulAug
SepOctNovDec

2003

JanFebMarApr
MayJunJulAug
SepOctNovDec

Photolog

Through the Looking-Glass
2010-10-12: Through the Looking-Glass
My radio speaks is binary!
2010-10-10: My radio speaks is binary!
Gigaminx: (present for my birthday)
2010-09-16: Gigaminx: (present for my birthday)
Trini on bike
2010-09-05: Trini on bike
Valporquero
2010-08-28: Valporquero
My new bike!
2010-08-22: My new bike!
Mario and Ana's wedding
2010-08-13: Mario and Ana's wedding
Canyoning in Guara
2010-08-07: Canyoning in Guara
Trini and Mari in Marbella
2010-08-05: Trini and Mari in Marbella
Trini and Chelo in Tabarca
2010-08-03: Trini and Chelo in Tabarca
Valid XHTML 1.1
Log in
Labels: SQL Perl how-to

(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;


New comment

Please, write down your name and what you want to say :-)

Name:
Comment: