Insert and Update Time of PostgreSQL Trace Record (Row and Tuple) – SPI Moddatetime Trigger

By Digoal

Background

Track the write time and last update time of the record

Methods:

When writing data, use the default value to fill in the current time.
When updating data, replace the NEW value of the tracked table time field automatically with the current time using before triggers.

C Trigger Function Example

With moddatetime, PostgreSQL's built-in tracking trigger function, the corresponding code can be found in the contrib directory of the code.

create extension moddatetime;    
    
DROP TABLE mdt;    
    
CREATE TABLE mdt (    
        id              int4,    
        idesc           text,    
        moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL    
);    
    
CREATE TRIGGER mdt_moddatetime    
        BEFORE UPDATE ON mdt    
        FOR EACH ROW    
        EXECUTE PROCEDURE moddatetime (moddate); -- 自动将moddate字段更新为最新时间    
    
INSERT INTO mdt VALUES (1, 'first');    
INSERT INTO mdt VALUES (2, 'second');    
INSERT INTO mdt VALUES (3, 'third');    
    
SELECT * FROM mdt;    
 id | idesc  |          moddate               
----+--------+----------------------------    
  1 | first  | 2019-08-17 00:13:19.799583    
  2 | second | 2019-08-17 00:13:19.861108    
  3 | third  | 2019-08-17 00:13:19.894494    
(3 rows)    
    
    
UPDATE mdt SET id = 4    
        WHERE id = 1;    
UPDATE mdt SET id = 5    
        WHERE id = 2;    
UPDATE mdt SET id = 6    
        WHERE id = 3;    
    
SELECT * FROM mdt;    
    
postgres=# SELECT * FROM mdt;    
 id | idesc  |          moddate               
----+--------+----------------------------    
  4 | first  | 2019-08-17 00:13:32.494098    
  5 | second | 2019-08-17 00:13:32.566837    
  6 | third  | 2019-08-17 00:13:32.614302    
(3 rows)

Codes:

/*  
moddatetime.c  
  
contrib/spi/moddatetime.c  
  
What is this?  
It is a function to be called from a trigger for the purpose of updating  
a modification datetime stamp in a record when that record is UPDATEd.  
  
Credits  
This is 95%+ based on autoinc.c, which I used as a starting point as I do  
not really know what I am doing.  I also had help from  
Jan Wieck <jwieck@debis.com> who told me about the timestamp_in("now") function.  
OH, me, I'm Terry Mackintosh <terry@terrym.com>  
*/

F.36.4. Moddatetime - Functions for Tracking Last Modification Timemoddatetime() is a trigger that stores the current time into a timestamp field. This can be useful for tracking the last modification time of a particular row within a table.

To use it, create a BEFORE UPDATE trigger using this function. Then, specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp, or timestamp with the time zone.

There is an example in moddatetime.example.

PL/pgSQL Trigger Function Example

create or replace function tg() returns trigger as $$    
declare     
begin    
  NEW.ts = clock_timestamp();    
  return new;    
end;    
$$ language plpgsql strict;    
    
create table t (id int, ts timestamp default now());    
    
CREATE TRIGGER ts    
        BEFORE UPDATE ON t    
        FOR EACH ROW    
        EXECUTE PROCEDURE tg();    
    
postgres=# insert into t values (1);    
INSERT 0 1    
postgres=# select * from t;    
 id |             ts                 
----+----------------------------    
  1 | 2019-08-17 00:12:45.246882    
(1 row)    
    
postgres=# update t set id=2;    
UPDATE 1    
postgres=# select * from t;    
 id |             ts                 
----+----------------------------    
  2 | 2019-08-17 00:12:50.963019    
(1 row)

Community

Insert and Update Time of PostgreSQL Trace Record (Row and Tuple) – SPI Moddatetime Trigger

Background

C Trigger Function Example

PL/pgSQL Trigger Function Example

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution