How to put a copy of a row into another table on update?

Question

I used this answer to autoupdate timestamp of last change How do I automatically update a timestamp in PostgreSQL.

Now, I wonder how I can copy the old version of the row to another table, like versions when this trigger runs? It would be inserted to versions table every time I modify the row.

Thanks.

score 1 · Accepted Answer · answered May 14 '20 at 14:26

This is a simple example:

CREATE TABLE versions (
   obsoleted timestamp with time zone NOT NULL,
   oldrow jsonb NOT NULL
);

CREATE FUNCTION vers_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO versions VALUES (current_timestamp, to_json(OLD));
   RETURN OLD;
END;$$;

CREATE TRIGGER vers_trig AFTER UPDATE OR DELETE ON atable
   FOR EACH ROW EXECUTE PROCEDURE vers_trig();

This will write the old version of the row to versions, together with the transaction timestamp of the statement that did it.

How to put a copy of a row into another table on update?

1 Answers1