sequence_helper.rb

# Helper for PostgreSQL Sequences
# USAGE:
#   SequenceHelper.nextval('somenumber_seq')
#   SequenceHelper.currval('somenumber_seq')
#   SequenceHelper.reset('somenumber_seq', 10)
class SequenceHelper < ActiveRecord::Base

  # Verify that the sequence exists or create it
  def self.verify(name, create = true, initial_value=1)
    name.downcase!
    result = connection.select_value("SELECT relname FROM pg_class WHERE relname='#{sanitize_sql(name)}'")
    # Create the sequence if not exist
    if create && result.blank?
      connection.execute("CREATE SEQUENCE #{sanitize_sql(name)}")
      self.reset(name, initial_value)
      return true
    else
      return !!result
    end
  end

  def self.reset(name, val=1, is_called=false)
    name.downcase!
    connection.execute("SELECT setval('#{name}', #{val}, #{is_called.inspect})") if verify(name, false)
  end

  def self.nextval(name)
    name.downcase!
    connection.select_value("SELECT nextval('#{sanitize_sql(name)}')") if verify(name, false)
  end

  def self.curval(name)
    name.downcase!
    connection.select_value("SELECT currval('#{sanitize_sql(name)}')") if verify(name, false)
  end
end