Scalar and aggregate functions transform data by performing calculations on their input values and returning a single value.
For an explanation of the difference between scalar and aggregate functions, and examples of each, see the W3Schools webpage on SQL functions: http://www.w3schools.com/SQL/sql_functions.asp.
In your code, any function of either type must have a specific method signature that includes arguments for a SQLite context reference, the number of arguments, and a reference to memory for storing the result. You must also define a qdb_function structure that describes the function. Consider this sample scalar function that determines the sorting direction ("forward" or "reverse") based on its user data:
typedef struct my_sort_cfg_s { unsigned rev_sort; } my_sort_cfg_t; static void get_my_sort_runtime( sqlite3_context *ctx, int narg, sqlite3_value **value) { my_sort_cfg_t *my_sort_cfg; char *config, char *key; size_t len; if ((my_sort_cfg = sqlite3_user_data(ctx)) == NULL) { return; } config = my_sort_cfg->rev_sort ? "forward" : "reverse"; len = strlen(config)+1; if (NULL != (key = sqlite3_malloc(len))) { memcpy(key, config, len); sqlite3_result_text(ctx, key, len, sqlite3_free); } } my_sort_cfg_t my_sort_cfg_data = { .rev_sort=0 }; struct qdb_function get_my_sort_runtime_cfg = { .name="get_my_sort_runtime_cfg", .encoding=SQLITE_UTF8, .narg=0, .arg=&my_sort_cfg_data, .func=get_my_sort_runtime, .step=NULL, .final=NULL };
The tag value in this case is get_my_sort_runtime_cfg, the function name as visible to SQL is also get_my_sort_runtime_cfg, and the C function that implements the scalar operation is get_my_sort_runtime(). This last function can retrieve its input data from the fourth field in the structure, my_sort_cfg_data, by calling sqlite3_user_data().
There can be multiple functions defined (in the same or different DLLs) but each must have a Function entry in the configuration object for the associated database as well as a struct qdb_function object with a unique name describing the function.
The qdb_function structure has these members:
struct qdb_function { char *name; int encoding; int narg; void *arg; void (*func)(struct sqlite3_context *, int, struct Mem **); void (*step)(struct sqlite3_context *, int, struct Mem **); void (*final)(struct sqlite3_context *); };