Changes tracking in PostGIS - basics

One of the huge benefit of using database is that many user can work on the database at the same time, e.g editing, updating database of airport taxiways and we can track their activity - who and when made changes.

Let’s assume that you want to build and maintain database of taxiways (co to jest taxiway). You not only want to model this part of real world but also want to store information when an by who (which user) specified feature has been created and changed.

Firstly we need database to store data about taxiways.

create table taxiway (
 twy_rec_id serial primary key,
 twy_name varchar(10) not null,
 twy_wid_m float not null,
 twy_sfc varchar(15),
 twy_lgt varchar(20),
 twy_rmk varchar(200),
 twy_pcn varchar(30),
 insert_ts timestamp default now(),
 insert_user varchar(20) default current_user,
 last_mod_ts timestamp,
 last_mod_user varchar(20),
 twy_boundary geography(POLYGON, 4326)
);

As you notice columns insert_ts, insert_user, last_mod_ts, last_mod_user keeps information about user who created (inserted) feature and last modified respectively.

The first column has default value   because we want to store information about user during inserting feature. Columns will be updated when feature will be updated (attributes or shape) and how to store this information I will explain later. Now we need at least two different users to notice any changes.  I have created 3 users: twy_editor1, twy_editor2, twy_editor3 with granted permission to insert and update statements on taxiway table of course.

To keep infromation when and who made changes we will use trigger.
First, let's create function that will be 'triggeresd' by trigger:

CREATE OR REPLACE FUNCTION taxiway_update_mod()
    RETURNS trigger as
$BODY$
begin
    new.last_mod_user = (select current_user);
    new.last_mod_ts=now();
    return new;
end;
$BODY$
language PLPGSQL;

and trigger:

CREATE TRIGGER taxiway_mod
    BEFORE UPDATE
    ON taxiway
    FOR EACH ROW
    EXECUTE PROCEDURE taxiway_update_mod();

select twy_name,
       twy_lgt as "Lighting",
       pcn as "Pavement Classification Number",       
       insert_ts as "Inserted",
       insert_user as "Inserted by",
       last_mod_ts as "Last modification", 
       last_mod_user as "Modified by"
from taxiway;

select twy_name,
       twy_lgt as "Lighting",
       pcn as "Pavement Classification Number",       
       insert_ts as "Inserted",
       insert_user as "Inserted by",
       last_mod_ts as "Last modification", 
       last_mod_user as "Modified by"
from taxiway
order by twy_rec_id;