Automatically updating a timestamp column in PostgreSQL

One of the great things about modern databases is you can let your database automate some of what used to only happen in application logic.  The example I love to show people is automatically updating a "last modified time" timestamp column in a table. 

This is easily accomplished if you always use the same name for those types of columns.  I like to use 'created' for the creation timestamp and 'modified' for the last modified time.  First we create a simple function:

	CREATE OR REPLACE FUNCTION update_modified_column()
	RETURNS TRIGGER AS $$
	BEGIN
	   NEW.modified = now(); 
	   RETURN NEW;
	END;
	$$ language 'plpgsql';

This function simply sets any column named 'modified' to the current timestamp for each row passed to it by the trigger. If you use the same column name consitently you only have to do this step once.  Now, you just have to create your trigger like so:

        CREATE TRIGGER update_customer_modtime BEFORE UPDATE
        ON customer FOR EACH ROW EXECUTE PROCEDURE 
        update_modified_column();

This technique is very useful when you don't want to have to rely on your application developers to always remember to update the time stamps.  You can just let PostgreSQL handle it for you.

You should note that you will have to create a separate trigger for each table, which isn't a big deal.  Also, the BEFORE UPDATE is very important.  If you attempt to use AFTER UPDATE you put yourself into an infinite loop!