Convenient methods to let PostgresQL manage created/updated_at

postgres_timestamp_defaults.rb

require 'active_support/core_ext/string/filters'

module PostgresTimestampDefaults
  def add_timestamp_defaults(table_name)
    add_default_now(table_name, :created_at)
    add_default_now(table_name, :updated_at)
    add_updated_at_trigger(table_name)
  end
  
  def add_default_now(table_name, column_name)
    set_not_null = <<-SQL.squish
      ALTER TABLE #{table_name}
        ALTER COLUMN #{column_name}
          SET NOT NULL;
    SQL
    set_default = <<-SQL.squish
      ALTER TABLE #{table_name}
        ALTER COLUMN #{column_name}
          SET DEFAULT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
    SQL
    execute set_not_null
    execute set_default
  end

  def add_updated_at_trigger(table_name)
    define_trigger_function = <<-SQL.squish
    	CREATE OR REPLACE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
    	BEGIN
    	   NEW.updated_at = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'; 
    	   RETURN NEW;
    	END;
    	$$ language 'plpgsql';
    SQL
    drop_trigger_if_exists = "DROP TRIGGER IF EXISTS set_updated_at_#{table_name} ON #{table_name};"
    create_trigger = <<-SQL.squish
      CREATE TRIGGER set_updated_at_#{table_name}
        BEFORE UPDATE ON #{table_name} FOR EACH ROW
        EXECUTE PROCEDURE set_updated_at_column();
    SQL
    execute define_trigger_function
    execute drop_trigger_if_exists
    execute create_trigger
  end
end

ActiveRecord::Migration.send(:include, PostgresTimestampDefaults)