Method: SQLite::Database#create_aggregate

Defined in:

lib/sqlite/database.rb

#create_aggregate(name, arity, step, finalize, type = nil) ⇒ 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.) If type is non-nil, it should be a value as described in #create_function.

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#set_result to store the result of the function.

Example:

step = proc do |func, value|
  func[ :total ] ||= 0
  func[ :total ] += ( value ? value.length : 0 )
end

finalize = proc do |func|
  func.set_result( func[ :total ] || 0 )
end

db.create_aggregate( "lengths", 1, step, finalize, :numeric )

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/sqlite/database.rb', line 411

def create_aggregate( name, arity, step, finalize, type=nil )
  case type
    when :numeric
      type = SQLite::API::NUMERIC
    when :text
      type = SQLite::API::TEXT
    when :args
      type = SQLite::API::ARGS
  end

  step_callback = proc do |func,*args|
    ctx = SQLite::API.aggregate_context( func )
    unless ctx[:__error]
      begin
        step.call( FunctionProxy.new( func, ctx ), *args )
      rescue Exception => e
        ctx[:__error] = e
      end
    end
  end

  finalize_callback = proc do |func|
    ctx = SQLite::API.aggregate_context( func )
    unless ctx[:__error]
      begin
        finalize.call( FunctionProxy.new( func, ctx ) )
      rescue Exception => e
        SQLite::API.set_result_error( func, "#{e.message} (#{e.class})" )
      end
    else
      e = ctx[:__error]
      SQLite::API.set_result_error( func, "#{e.message} (#{e.class})" )
    end
  end

  SQLite::API.create_aggregate( @handle, name, arity,
    step_callback, finalize_callback )

  SQLite::API.function_type( @handle, name, type ) if type

  self
end