Create a trigger on all the last_modified columns in PostgreSQL

Question

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

Answer 1

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

Answer 2

Technically it is possible. For example:

--drop table if exists t1;
--drop table if exists t2;
--drop function if exists foo();
create table t1(x int, q int);
create table t2(y int, q int);

create function foo() returns trigger language plpgsql as $foo$
begin
  raise info '%', tg_argv[0];
  raise info '%', new; 
  execute $$select (b).* from (select $1 #= hstore($2||'=>666')) as a(b)$$ using new, tg_argv[0] into new;
  raise info '%', new;
  return new;
end $foo$;

create trigger tg_def before insert on t1 for each row execute procedure foo('x');
create trigger tg_def before insert on t2 for each row execute procedure foo('y');

nd@postgres=# insert into t1 values(1,2);
INFO:  x
INFO:  (1,2)
INFO:  (666,2)
INSERT 0 1
nd@postgres=# insert into t2 values(1,3);
INFO:  y
INFO:  (1,3)
INFO:  (666,3)
INSERT 0 1
nd@postgres=# select * from t2;
╔═════╤═══╗
║  y  │ q ║
╠═════╪═══╣
║ 666 │ 3 ║
╚═════╧═══╝

You have to install hstore extension for this code.

However it could to extremely slowing down the DMLs because of execute using.