Thread: Implement updated column in all tables

I have the following column in all my tables which contains data of last
update of this row:

updated timestamp without time zone DEFAULT now() NOT NULL,

Hor to force Postgres to update this column automatically  with now() value
every time when row is updated ?

I have about 100 tables and don't like to write 100 triggers.

On Thu, Jul 07, 2005 at 04:25:31PM +0300,
 Andrus <noeetasoftspam@online.ee> wrote
 a message of 11 lines which said:

> I have the following column in all my tables which contains data of
> last update of this row:

Me too.

> I have about 100 tables and don't like to write 100 triggers.

I use a templating engine (Cheetah, http://www.cheetahtemplate.org/
but you are free to use cpp, m4, anything) to manage my SQL files, so
it is quite easy to create N triggers, no matter how large N is.

I do not know a pure PostgreSQL solution. Remember that the problem is
not obvious to solve in a general way. For instance, there are
typically fields for which you do not regard a SQL update as a "real"
(worthy of recording) update.

On Thu, 2005-07-07 at 08:25, Andrus wrote:
> I have the following column in all my tables which contains data of last
> update of this row:
>
> updated timestamp without time zone DEFAULT now() NOT NULL,
>
> Hor to force Postgres to update this column automatically  with now() value
> every time when row is updated ?
>
> I have about 100 tables and don't like to write 100 triggers.


This will work.  Just replace .lm with whatever you want to name your
last modified field.  Assuming all 100 tables have the same name for the
last modified field, applying it should be as easy as a shell script.


-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
    BEGIN
        new.lm :=''now'';
        RETURN new;
    END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
    id int primary key,
    fluff text,
    lm timestamp without time zone
);


--TRIGGER --

CREATE TRIGGER dtest
  BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
    modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
  1 | this is a test       | 2003-04-02 10:33:12.577089
  2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
  3 | this is a test | 2003-04-02 10:34:52.219963  [1]
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
  2 | now is the time | 2003-04-02 10:38:06.259443 [2]
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
  3 | this is a test | 2003-04-02 10:36:15.45687 [3]

[1] The timestamp has changed for this record when we changed the id field.
[2] The timestamp also changes for the fluff field.
[3] We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it