Scalar and aggregate functions

Updated: April 19, 2023

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 *);
};
name
The name used for this function in SQL statements. The name is limited to 255 bytes, exclusive of the null-terminator, and it can't contain any special tokens or start with a digit. Any attempt to create a function with an invalid name results in an SQLITE_ERROR error.
encoding
The character encoding of strings passed to the function. Can be one of:
  • SQLITE_UTF8
  • SQLITE_UTF16
  • SQLITE_UTF16BE
  • SQLITE_UTF16LE
narg
The number of arguments that the function takes. A value of -1 means any number of arguments. The maximum number of arguments is 127; values larger than 127 result in an SQLITE_ERROR error.
arg
An arbitrary pointer. To access this pointer, the function code can call sqlite_user_data().
func, step, final
Pointers to your function code. A scalar function requires an implementation of the func callback only; NULL pointers should be passed as the step and final arguments. An aggregate function requires an implementation of step and final, and NULL should be passed for func. Specifying an inconsistent set of callback values, such as a func and a final, or a step but no final, results in an SQLITE_ERROR error.