PostgreSQL Snippets

# Update modified_date column on update
CREATE OR REPLACE FUNCTION update_modified_date_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_date = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

or even better but more costly compute if only if content has changed

CREATE OR REPLACE FUNCTION update_modified_date_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.modified_date = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';


BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE  update_modified_date_column();