update Timestamp updated whenever the table is updated

[Campbell, Lance]

PostgreSQL 9.2.3
I would like to have a generic strategy for updating a timestamp field on some tables whenever the table is updated. Is there a recommended strategy for doing this other than via the SQL UPDATE command? I think I read something about RULES. In the below example I have a table called test_table. I would ideally like to update the field called "updated_timestamp" whenever an update occurs by doing:

UPDATE test_table SET updated_timestamp=now() WHERE id=?

While looking at the concept of RULES how do I say after an update happens update that particular record and not all of the records in test_table.

Example table:
CREATE TABLE test_table
(
id integer NOT NULL,
field1 character varying NOT NULL,
field2 character varying NOT NULL,
updated_timestamp timestamp with time zone DEFAULT now(),
created_timestamp timestamp with time zone DEFAULT now()
);


Thanks,

Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/>

[Kevin Grittner]

Post by Campbell, Lance
I would like to have a generic strategy for updating a timestamp
field on some tables whenever the table is updated.  Is there a
recommended strategy for doing this other than via the SQL UPDATE
command?
CREATE TABLE test_table
(
   id integer NOT NULL,
   field1 character varying NOT NULL,
   field2 character varying NOT NULL,
   updated_timestamp timestamp with time zone DEFAULT now(),
   created_timestamp timestamp with time zone DEFAULT now()
);

A BEFORE UPDATE trigger is probably what you want.  You could write
a single trigger function which could be attached to all tables
with the updated_timestamp column.  For example:

CREATE FUNCTION set_updated_timestamp()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_timestamp := now();
  RETURN NEW;
END;
$$;

Note that the above function depends on a column name, but not a
table name.  You link it to each table like this:

CREATE TRIGGER test_table_update_timestamp
  BEFORE UPDATE ON test_table
  FOR EACH ROW EXECUTE PROCEDURE set_updated_timestamp();

Post by Campbell, Lance
I think I read something about RULES.

You generally want to avoid RULES, especially where a trigger works
so well.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company