[postgis-users] Simple PostgreSQL versioning

[Ivan Mincik]

Dear PostGIS users,
I have created set of database functions for managing editing history
of my PostgreSQL tables, including PostGIS geometry tables. Currently
it is in state of 'proof of concept', but usable. I would like to
share it and get some feedback. I was inspired by versioning functions
of Horst Duester and Andreas Neumann, but implemented in other way.

If You are interested, please download here:
http://gista.sk/home/ivo/projects/simple_pg_versioning/simple_pg_versioning-0.1~r18.tar.gz
Package is containing some README instructions and it is tested only
on my Debian Lenny computers (PostgreSQL 8.3, PostGIS 1.3.3).

How does it work?
1. After loading functions to Your DB, master history table is created
in 'sv_history' schema for every versioning enabled tables.
2. You can enable versioning using  'SV_CreateHistory('<schema>',
'<table>')' function (run SELECT SV_CreateHistory('<schema>',
'<table>');)
3. Edit Your table ....
4. For retrieving table's state in particulal time use
'<schema>.<table>_AtTime('<timestamp>')' function. (run SELECT * FROM
<schema>.<table>_AtTime('<timestamp>');)

So, in case of table called 'mytable' in schema 'gis':
1. Enable versioning:  SELECT SV_CreateHistory('gis', 'mytable');
2. Get the table's state from today's morning: SELECT * FROM
gis.mytable_AtTime('2010-10-18 08:00:00');

I am not database programmer and I lack lot of PL/PgSQL skils,
therefore I used my preferred PL/Python language to solve my ideas.
Any improvements, suggestions, criticism is welcome.

Ivan