"Campbell, Lance" <la...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
