Re: update Timestamp updated whenever the table is updated

"Campbell, Lance" <lance(at)illinois(dot)edu> wrote:

> 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?

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

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

> 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