1.Dd December 19, 2018 2.Dt SQLITE3_CREATE_FUNCTION 3 3.Os 4.Sh NAME 5.Nm sqlite3_create_function , 6.Nm sqlite3_create_function16 , 7.Nm sqlite3_create_function_v2 , 8.Nm sqlite3_create_window_function 9.Nd Create Or Redefine SQL Functions 10.Sh SYNOPSIS 11.Ft int 12.Fo sqlite3_create_function 13.Fa "sqlite3 *db" 14.Fa "const char *zFunctionName" 15.Fa "int nArg" 16.Fa "int eTextRep" 17.Fa "void *pApp" 18.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 19.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 20.Fa "void (*xFinal)(sqlite3_context*) " 21.Fc 22.Ft int 23.Fo sqlite3_create_function16 24.Fa "sqlite3 *db" 25.Fa "const void *zFunctionName" 26.Fa "int nArg" 27.Fa "int eTextRep" 28.Fa "void *pApp" 29.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 30.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 31.Fa "void (*xFinal)(sqlite3_context*) " 32.Fc 33.Ft int 34.Fo sqlite3_create_function_v2 35.Fa "sqlite3 *db" 36.Fa "const char *zFunctionName" 37.Fa "int nArg" 38.Fa "int eTextRep" 39.Fa "void *pApp" 40.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 41.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 42.Fa "void (*xFinal)(sqlite3_context*)" 43.Fa "void(*xDestroy)(void*) " 44.Fc 45.Ft int 46.Fo sqlite3_create_window_function 47.Fa "sqlite3 *db" 48.Fa "const char *zFunctionName" 49.Fa "int nArg" 50.Fa "int eTextRep" 51.Fa "void *pApp" 52.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 53.Fa "void (*xFinal)(sqlite3_context*)" 54.Fa "void (*xValue)(sqlite3_context*)" 55.Fa "void (*xInverse)(sqlite3_context*,int,sqlite3_value**)" 56.Fa "void(*xDestroy)(void*) " 57.Fc 58.Sh DESCRIPTION 59These functions (collectively known as "function creation routines") 60are used to add SQL functions or aggregates or to redefine the behavior 61of existing SQL functions or aggregates. 62The only differences between the three "sqlite3_create_function*" routines 63are the text encoding expected for the second parameter (the name of 64the function being created) and the presence or absence of a destructor 65callback for the application data pointer. 66Function sqlite3_create_window_function() is similar, but allows the 67user to supply the extra callback functions needed by aggregate window functions. 68.Pp 69The first parameter is the database connection to 70which the SQL function is to be added. 71If an application uses more than one database connection then application-defined 72SQL functions must be added to each database connection separately. 73.Pp 74The second parameter is the name of the SQL function to be created 75or redefined. 76The length of the name is limited to 255 bytes in a UTF-8 representation, 77exclusive of the zero-terminator. 78Note that the name length limit is in UTF-8 bytes, not characters nor 79UTF-16 bytes. 80Any attempt to create a function with a longer name will result in 81SQLITE_MISUSE being returned. 82.Pp 83The third parameter (nArg) is the number of arguments that the SQL 84function or aggregate takes. 85If this parameter is -1, then the SQL function or aggregate may take 86any number of arguments between 0 and the limit set by sqlite3_limit(SQLITE_LIMIT_FUNCTION_ARG). 87If the third parameter is less than -1 or greater than 127 then the 88behavior is undefined. 89.Pp 90The fourth parameter, eTextRep, specifies what text encoding 91this SQL function prefers for its parameters. 92The application should set this parameter to SQLITE_UTF16LE 93if the function implementation invokes sqlite3_value_text16le() 94on an input, or SQLITE_UTF16BE if the implementation 95invokes sqlite3_value_text16be() on an input, 96or SQLITE_UTF16 if sqlite3_value_text16() 97is used, or SQLITE_UTF8 otherwise. 98The same SQL function may be registered multiple times using different 99preferred text encodings, with different implementations for each encoding. 100When multiple implementations of the same function are available, SQLite 101will pick the one that involves the least amount of data conversion. 102.Pp 103The fourth parameter may optionally be ORed with SQLITE_DETERMINISTIC 104to signal that the function will always return the same result given 105the same inputs within a single SQL statement. 106Most SQL functions are deterministic. 107The built-in random() SQL function is an example of a function 108that is not deterministic. 109The SQLite query planner is able to perform additional optimizations 110on deterministic functions, so use of the SQLITE_DETERMINISTIC 111flag is recommended where possible. 112.Pp 113The fifth parameter is an arbitrary pointer. 114The implementation of the function can gain access to this pointer 115using sqlite3_user_data(). 116.Pp 117The sixth, seventh and eighth parameters passed to the three "sqlite3_create_function*" 118functions, xFunc, xStep and xFinal, are pointers to C-language functions 119that implement the SQL function or aggregate. 120A scalar SQL function requires an implementation of the xFunc callback 121only; NULL pointers must be passed as the xStep and xFinal parameters. 122An aggregate SQL function requires an implementation of xStep and xFinal 123and NULL pointer must be passed for xFunc. 124To delete an existing SQL function or aggregate, pass NULL pointers 125for all three function callbacks. 126.Pp 127The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue 128and xInverse) passed to sqlite3_create_window_function are pointers 129to C-language callbacks that implement the new function. 130xStep and xFinal must both be non-NULL. 131xValue and xInverse may either both be NULL, in which case a regular 132aggregate function is created, or must both be non-NULL, in which case 133the new function may be used as either an aggregate or aggregate window 134function. 135More details regarding the implementation of aggregate window functions 136are available here. 137.Pp 138If the final parameter to sqlite3_create_function_v2() or sqlite3_create_window_function() 139is not NULL, then it is destructor for the application data pointer. 140The destructor is invoked when the function is deleted, either by being 141overloaded or when the database connection closes. 142The destructor is also invoked if the call to sqlite3_create_function_v2() 143fails. 144When the destructor callback is invoked, it is passed a single argument 145which is a copy of the application data pointer which was the fifth 146parameter to sqlite3_create_function_v2(). 147.Pp 148It is permitted to register multiple implementations of the same functions 149with the same name but with either differing numbers of arguments or 150differing preferred text encodings. 151SQLite will use the implementation that most closely matches the way 152in which the SQL function is used. 153A function implementation with a non-negative nArg parameter is a better 154match than a function implementation with a negative nArg. 155A function where the preferred text encoding matches the database encoding 156is a better match than a function where the encoding is different. 157A function where the encoding difference is between UTF16le and UTF16be 158is a closer match than a function where the encoding difference is 159between UTF8 and UTF16. 160.Pp 161Built-in functions may be overloaded by new application-defined functions. 162.Pp 163An application-defined function is permitted to call other SQLite interfaces. 164However, such calls must not close the database connection nor finalize 165or reset the prepared statement in which the function is running. 166.Sh SEE ALSO 167.Xr sqlite3 3 , 168.Xr sqlite3_limit 3 , 169.Xr sqlite3_user_data 3 , 170.Xr sqlite3_value_blob 3 , 171.Xr SQLITE_DETERMINISTIC 3 , 172.Xr SQLITE_LIMIT_LENGTH 3 , 173.Xr SQLITE_OK 3 , 174.Xr SQLITE_UTF8 3 175