chickadee » sqlite3 » define-function

Registers a new SQL function identified by NAME for use in the context of the given database handle. If STEP-PROC and SEED are given, the new function becomes an aggregate function. Once registered, functions cannot be deleted.

N is the number of parameters the new SQL function takes or -1 to allow any number of arguments.

PROC should have the signature (PROC . PARAMS) => OBJECT. It is called with the N parameters given to the SQL function converted into Scheme objects like by column-data. The return value is converted into an SQLite data object like by bind!. A return value satisfying sql-null? corresponds to NULL in SQLite.

STEP-PROC should have the signature (STEP-PROC SEED PARAMS) => SEED. It is called with the parameters given to the SQL function for every row being processed. The seed value passed is initially the one given as an argument to define-function; for subsequent calls it is the last value returned by STEP-PROC and after completion of FINAL-PROC it will be the initial value again.

FINAL-PROC should have the signature (FINAL-PROC SEED) => OBJECT and transforms the last seed value into the value to be returned from the aggregate function. If it is not explicitly specified, STEP-PROC defaults to the identity function.

As PROC, STEP-PROC and FINAL-PROC will be called in a callback context from within step!, safety measures are installed to avoid throwing any exceptions, invoking continuations or returning invalid values from them. Attempts to do such things will result in NULL return values and warning messages.