In my current project, there is a need for tracking data changes in the PostgreSQL tables. The end goal is, if a row changes, we copy the previous row before the change transaction completes and write it to a logging table. We will accomplish this with in the following steps:
- Creating a table LIKE our table that needs logging
- Create a function for our table
- Apply that function as a trigger
Table Like
First we need an example table to get started with. For a simple example, lets use a basic address table.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table address | |
( | |
address_id integer not null, | |
type varchar(100), | |
street1 varchar(120) not null, | |
street2 varchar(120), | |
street3 varchar(120), | |
street4 varchar(120), | |
city varchar(80), | |
po_box_code varchar(20) not null, | |
phone_number varchar(50), | |
date_created timestamp with time zone not null default current_timestamp | |
); |
This basic table has enough constraints to make a decent example. We need to create a copy of this table, one where the columns are the same name and type, but without all of the constraints. Luckily for us, PostgreSQL provides a feature for just a situation. For this, we want to use the like_option for the CREATE TABLE statement. According the latest documentation (PostgreSQL 12) at the time of writing this post:
The LIKE
clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.
Unlike INHERITS
, the new table and original table are completely decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.
Also unlike INHERITS
, columns and constraints copied by LIKE
are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE
clause, an error is signaled.
The optional like_option
clauses specify which additional properties of the original table to copy. Specifying INCLUDING
copies the property, specifying EXCLUDING
omits the property. EXCLUDING
is the default. If multiple specifications are made for the same kind of object, the last one is used.
We will want to exclude all constraints so that when our trigger fires, it can write any data to the columns without worrying if those columns are valid. The resulting table definition looks like the following:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table logging_address | |
( | |
like address EXCLUDING CONSTRAINTS, | |
operation char(10) not null, | |
date_operated timestamp with time zone not null default current_timestamp | |
); |
Logging Function
Next, there needs to be a trigger that logs the data. To create a new trigger in PostgreSQL, you follow these steps:
-
- First, create a trigger function using
CREATE FUNCTION
statement. - Second, bind the trigger function to a table by using
CREATE TRIGGER
statement.
- First, create a trigger function using
A trigger function is similar to an ordinary function. However, a trigger function does not take any argument and has a return value with the type of trigger
. Inside this trigger function, insert the old data into the logging table. This makes the trigger function as follows:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create function address_trigger_function() | |
returns trigger as $$ | |
BEGIN | |
insert into logging_address_address (address_id, type, street1, street2, street3, street4, city, po_box_code, phone_number, date_created, operation) | |
values (old.address_id, old.type, old.street1,old.street2,old.street3,old.street4,old.city,old.po_box_code,old.phone_number, old.date_created,TG_OP); | |
RETURN NEW; | |
end; | |
$$ LANGUAGE plpgsql; |
TG_OP is Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.
Implementing the Trigger
As we said earlier, Second, bind the trigger function to a table by using CREATE TRIGGER
statement. This part is fairly easy.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TRIGGER address_versioning_trigger | |
BEFORE UPDATE OR DELETE ON location.address | |
FOR EACH ROW EXECUTE PROCEDURE address_trigger_function(); |
Now, whenever you insert, update, or delete a record in the address table, the operation is logged in the logging address table.