Table of Contents for

Using SQLite

Definition

int sqlite3_create_function(   sqlite3* db, const char* name, int num_param,
        int text_rep, void* udp, func_func, step_func, final_func );
int sqlite3_create_function16( sqlite3* db, const void* name, int num_param,
        int text_rep, void* udp, func_func, step_func, final_func );

void func_func(  sqlite3_context* ctx, int argc, sqlite3_value** argv );
void step_func(  sqlite3_context* ctx, int argc, sqlite3_value** argv );
void final_func( sqlite3_context* ctx );

Description

These functions are used to define custom SQL scalar functions or SQL aggregate functions. This is done by registering C function pointers that implement the desired SQL function.

The only difference between sqlite3_create_function() and sqlite3_create_function16() is the text encoding used to define the function name (the second parameter). The encoding used when calling the registered functions is determined by the fourth parameter. Either function can be used to register functions that understands any of the given encodings.

A single call to one of these functions can be used to define either an SQL scalar function or an SQL aggregate function, but not both. A scalar function is defined by providing a valid func_func parameter and setting step_func and final_func to NULL. Conversely, an aggregate function is defined by providing a valid step_func and final_func while setting func_func to NULL.

By providing different values for the num_param or text_rep (or both) parameters, different functions can be registered under the same SQL function name. SQLite will choose the closest fit, first by parameter number and then by text representation. An explicit parameter number is considered a better fit than a variable length function. Text representations are judged by those that require the least amount of conversion.

Both scalar and aggregate functions can be defined under the same name, assuming they accept a different number of parameters. SQLite uses this functionality internally to define both scalar and aggregate max() and min() functions.

A function can be redefined by registering a new set of function pointers under the same name, parameter number, and text representation. This functionality can be used to override existing functions (including built-in functions). To unregister a function, simply pass in all NULL function pointers.

Finally, there is one significant limitation to sqlite3_create_function(). Although new functions can be defined at any time, it is not legal to redefine or delete a function when there are active statements. Because existing statements may contain references to the existing SQL functions, all prepared statements must be invalidated when redefining or deleting a function. If there are active statements in the middle of executing, this is not possible, and will cause sqlite3_create_function() to fail.

For more information on how to write custom SQL scalar and aggregate functions, see Chapter 9.

See Also

sqlite3_create_collation() [C API, Ap G], sqlite3_user_data() [C API, Ap G], sqlite3_context_db_handle() [C API, Ap G], sqlite3_value_xxx() [C API, Ap G], sqlite3_result_xxx() [C API, Ap G]