PL/pgSQL helper to create a trigger filling an history table with all changes over time.
2019 experimentation, archived
- For a given table
{schema}.{table}and an history table named{schema}.{table}_h pghs_create_history_trigger(_table,_schema)creates a trigger filling{schema}.{table}_hwhen INSERT, UPDATE or DELETE are performed on{schema}.{table}
{schema}.{table}and{schema}.{table}_hhave the same columns with the same order- The following attributes are required on each table (they are managed by the trigger)
| name | type | description |
|---|---|---|
| pghs_version | integer NOT NULL | 1, 2, 3... |
| pghs_date | timestamp | NOW() (transaction time) |
| pghs_state | char(1) | 'I', 'U' or 'D' |
(id,pghs_version)is the primary key of{schema}.{table}_h(it allows conflict detection)
createdb pghs
psql -d pghs -f pghs.sql
psql -d pghs -f sample/poi.schema.sqlpsql -d pghs -f sample/poi.data.sql(you may also edit table using QuantumGIS for example)
Each time a row is INSERTED, UPDATED or DELETED in sample.poi, the row is duplicated in sample.poi_h
No need to re-create the trigger, but don't forget to update both tables :
ALTER table sample.poi add column title text;
ALTER table sample.poi_h add column title text;select * from sample.poi_h t
where t.pghs_version = (
select max(pghs_version)
from sample.poi_h t2
where t2.id = t.id
and t2.pghs_date < '2019-01-31 11:36:00'::timestamp
)
;They are no requirements on the id type. You may use uuid instead of serial.
Feel free to adapt "pghs_create_history_trigger" creating a dedicated version for your database!
This code is an attempt to generalize the following approach :
Other kind of audit trigger exists :