Skip to content

Instantly share code, notes, and snippets.

@cee-dub
Created January 29, 2012 20:53
Show Gist options
  • Save cee-dub/1700591 to your computer and use it in GitHub Desktop.
Save cee-dub/1700591 to your computer and use it in GitHub Desktop.
Convenient methods to let PostgresQL manage created/updated_at
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment