An example of a simple versioning of PostgreSQL entries

Once I saw the implementation of the "record history" - versioning, on the side of the program, working with SQL database. Before changing the record, the old version was obtained from the database, written in XML and the resulting XML string was written into a separate version table.
 
 
Initially, in his program, he planned to make versioning sometime later, which was not an urgent need. I remember there was a desire somewhere to use the jsonb data type, as soon as I thought of a simple and concise implementation of versioning on the SQL side, I could not do it. Just one table of versions with 5 columns and one trigger function in 3 lines of code.
 
 
To describe the implementation of a single version table is not enough, so I'll have to describe a few more tables for an example.
 
 
Almost in all databases, with rare exceptions, there is a table users - users. The history of changes - versions of the user is useful to store, for example, for the possibility to roll back to the old version, by the user's own forces.
 
 
Example of the user table:
 
 
An example of a simple versioning of PostgreSQL entries
 
 
The last two fields in the picture are needed for the version table, they can also be called "version author" and "version date", but, if desired, you can do without them.
 
The full code of the example is at
+ 0 -

Add comment