"Campbell, Lance" <lance@illinois.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