[postgis-users] Postgresql/Postgis: Trigger for historization/versioning

[celati Laurent]

Good evening,
I work with Postgresql 13, Postgis and Qgis 3.22.
My need is to set up within my Postgis database (used for maps production),
triggers for automation of data historization/versioning.
The idea: For instance within a table 'BOREHOLE' (geometry: points),
different updates of the table are made over time.

The version change occurs in the event that:
• new objects are added or deleted in the table (INSERT / DELETE)
• objects are updated by new versions of objects canceling and replacing
existing objects (UPDATE).
Sometimes, these UPDATE only concern the geometry column (change of
location). Sometimes other fields/attributes (type of borehole, technical
referent, name of the campaign for the borehole, start date of
construction, name of the municipality where the borehole is located, etc.).

=> These INSERT/DELETE/UPDATE must have the effect, within the "archive"
schema (schema dedicated to the storage of historical/versioned
tables/objects), the creation of a new table "BOREHOLE_V1", " BOREHOLE
 _V2", "  BOREHOLE  _V3",etc.

*Requirements:*
• The objective is NOT to perform a version upgrade at each modification
(UPDATE) in the table, but to identify structuring phases.
In other words, the idea is that the administrator can decide, trigger when
he deems relevant, the trigger/history-versioning process.

• "Table versioning" way is recommended": the versioning tables (ARCHIVE
schema) will be made up of modified elements AND also unmodified objects
from the "BOREHOLE" table.
This choice was done in particular to facilitate the restoration of
versioned objects at the level of archived qgis projects.
*Advantage*: possibility of referring to a specific phase of the Instant T
project.
*Disadvantage*: duplication of objects even unmodified within the archive
table.

The administrator could activate a historization action when it seems
relevant to him. For example at the end of an Qgis editing session.

Could someone guide me, direct me to the methods offered by
PostgreSQL/Postgis (or even Qgis) likely to satisfy my needs?
A big thank-you. ;-) Hoping to have been sufficiently clear.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220918/458c7b50/attachment.htm>