Adding New SQL Functions

SQLite allows you to add new functions to the SQL language that can subsequently be used in your queries.

Registering Functions

 int sqlite_create_function(   sqlite *db,   const char *zName,   int nArg,   void (*xFunc)(sqlite_func*,int,const char**),   void *pUserData ); 

Creates a regular function in SQL from the function pointed to by xFunc.

 int sqlite_create_aggregate(   sqlite *db,   const char *zName,   int nArg,   void (*xStep)(sqlite_func*,int,const char**),   void (*xFinalize)(sqlite_func*),   void *pUserData ); 

Creates an aggregating function with function xStep executed once for each row returned by the query, and xFinalize invoked once after all rows have been returned.

The xFunc and xStep arguments are pointers to functions with the following prototype.

 void xFunc(   sqlite_func *context,   int argc,   const char **argv ); 

The finalize function requires only the context argument.

 void xFinalize(sqlite_func *context); 

The context argument is an opaque data type sqlite_func.

 typedef struct sqlite_func sqlite_func; 

Setting Return Values

 char *sqlite_set_result_string(   sqlite_func *p,   const char *zResult,   int n ); void sqlite_set_result_int(   sqlite_func *p,   int iResult ); void sqlite_set_result_double(   sqlite_func *p,   double rResult ); 

Use the appropriate function for the data type that is to be returned to SQL.

 void sqlite_set_result_error(   sqlite_func *p,   const char *zMsg,   int n); 

Returns an error code to SQLite.

The integer n parameter to sqlite_set_result_string() and sqlite_set_result_error() is the number of characters to be returned. A negative value will return up to and including the first \0 character.

Referencing Arbitrary Data

 void *sqlite_user_data(sqlite_func *p); 

Returns the pUserData pointer given in the corresponding sqlite_create_function() or sqlite_create_aggregate() call.

 void *sqlite_aggregate_context(   sqlite_func *p,   int nBytes ); 

Allocates memory that is unique to a particular instance of the SQL function being called. Memory allocated is automatically cleaned up when the finalize function is invoked.