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 );db
A database connection.
name
The name of the collation in UTF-8 or UTF-16, depending on the function used.
num_param
The number of expected parameters in the SQL function. If the value is ‒1, any number of parameters will be accepted.
text_rep
The text representation best suited to the
function(s). This value can be one of SQLITE_UTF8, SQLITE_UTF16 (native order), SQLITE_UTF16BE, SQLITE_UTF16LE,
or
SQLITE_
ANY.
udp
An application-defined user-data pointer. This
value can be extracted from the ctx
parameter
using the sqlite3_user_data() function.
func_func
A function pointer to an application-defined
scalar SQL function implementation. If this is
non-NULL, the step_func and final_func parameters must be
NULL.
step_func
A function pointer to an application-defined
aggregate step function implementation. If this is
non-NULL, the func_func parameter must be NULL and
the final_func
parameter must be non-NULL.
final_func
A function pointer to an application-defined
aggregate finalize function implementation. If
this is non-NULL, the func_func parameter must be NULL and
the step_func
parameter must be non-NULL.
ctx
An SQL function context, provided by the SQLite library.
argc
The number of parameters given to the SQL function.
argv
The parameter values passed into the SQL function.
sqlite3_create_function[16]())An SQLite result code.
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.
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]