PostgreSQL: Auto update updated_at with custom column

Question

Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger I had to auto-update my updated_at column.

This is the function I had:

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

And I would add a trigger like:

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE update_updated_at();

But now my column is named users_updated_at and this doesn't work. My question is is there any way that I can pass the column name to the trigger and update the passed column, or is there is any other way that I am not aware of?

Answer 1

Use the spi module's moddatetime

spi module, moddatetime extension

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;
ALTER TABLE users ALTER timestamp_at SET DEFAULT now();
DROP TRIGGER IF EXISTS
  update_users_updated_at ON users;
CREATE TRIGGER mdt_users
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (timestamp_at);