Postgresql – Create a trigger on all the last_modified columns in PostgreSQL

postgresqlrow-modification-timetrigger

In PostgreSQL 9.5, I have tables with columns in the form 

prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')

I was looking for a way to set the last modified value automatically updated at each update of the rows, and I found this nice post that defined the function:

CREATE OR REPLACE FUNCTION update_modified_column() 
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW; 
END;
$$ language 'plpgsql';

Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g.

CREATE OR REPLACE FUNCTION update_modified_column(varchar column)   
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.column = now();
        RETURN NEW; 
    END;
$$ language 'plpgsql';

Best Answer

spi module: moddatetime

moddatetime — Functions for Tracking Last Modification Time

moddatetime() 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, create a BEFORE UPDATE trigger using this function. Specify a single trigger argument: the name of the column to be modified. The column must be of type timestamp or timestamp with time zone.

There is an example in moddatetime.example.

Example / Synopsis

From the above referenced file,

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);

INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');

SELECT * FROM mdt;

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;

Your Application

So this is what you would need to.

CREATE EXTENSION moddatetime;

CREATE TRIGGER mdt_table
  BEFORE UPDATE ON table
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (prefix_last_modified);
Related Question