Method: SQLite3::Database#create_aggregate

Defined in:

lib/sqlite3/database.rb

#create_aggregate(name, arity, step = nil, finalize = nil, text_rep = Constants::TextRep::ANY, &block) ⇒ Object

Creates a new aggregate function for use in SQL statements. Aggregate functions are functions that apply over every row in the result set, instead of over just a single row. (A very common aggregate function is the “count” function, for determining the number of rows that match a query.)

The new function will be added as name, with the given arity. (For variable arity functions, use -1 for the arity.)

The step parameter must be a proc object that accepts as its first parameter a FunctionProxy instance (representing the function invocation), with any subsequent parameters (up to the function’s arity). The step callback will be invoked once for each row of the result set.

The finalize parameter must be a proc object that accepts only a single parameter, the FunctionProxy instance representing the current function invocation. It should invoke FunctionProxy#result= to store the result of the function.

Example:

db.create_aggregate( "lengths", 1 ) do
  step do |func, value|
    func[ :total ] ||= 0
    func[ :total ] += ( value ? value.length : 0 )
  end

  finalize do |func|
    func.result = func[ :total ] || 0
  end
end

puts db.get_first_value( "select lengths(name) from table" )

See also #create_aggregate_handler for a more object-oriented approach to aggregate functions.

# File 'lib/sqlite3/database.rb', line 363

def create_aggregate( name, arity, step=nil, finalize=nil,
  text_rep=Constants::TextRep::ANY, &block )

  factory = Class.new do
    def self.step( &block )
      define_method(:step, &block)
    end

    def self.finalize( &block )
      define_method(:finalize, &block)
    end
  end

  if block_given?
    factory.instance_eval(&block)
  else
    factory.class_eval do
      define_method(:step, step)
      define_method(:finalize, finalize)
    end
  end

  proxy = factory.new
  proxy.extend(Module.new {
    attr_accessor :ctx

    def step( *args )
      super(@ctx, *args)
    end

    def finalize
      super(@ctx)
    end
  })
  proxy.ctx = FunctionProxy.new
  define_aggregator(name, proxy)
end