1.Dd January 24, 2024 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.In sqlite3.h 12.Ft int 13.Fo sqlite3_create_function 14.Fa "sqlite3 *db" 15.Fa "const char *zFunctionName" 16.Fa "int nArg" 17.Fa "int eTextRep" 18.Fa "void *pApp" 19.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 20.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 21.Fa "void (*xFinal)(sqlite3_context*)" 22.Fc 23.Ft int 24.Fo sqlite3_create_function16 25.Fa "sqlite3 *db" 26.Fa "const void *zFunctionName" 27.Fa "int nArg" 28.Fa "int eTextRep" 29.Fa "void *pApp" 30.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 31.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 32.Fa "void (*xFinal)(sqlite3_context*)" 33.Fc 34.Ft int 35.Fo sqlite3_create_function_v2 36.Fa "sqlite3 *db" 37.Fa "const char *zFunctionName" 38.Fa "int nArg" 39.Fa "int eTextRep" 40.Fa "void *pApp" 41.Fa "void (*xFunc)(sqlite3_context*,int,sqlite3_value**)" 42.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 43.Fa "void (*xFinal)(sqlite3_context*)" 44.Fa "void(*xDestroy)(void*)" 45.Fc 46.Ft int 47.Fo sqlite3_create_window_function 48.Fa "sqlite3 *db" 49.Fa "const char *zFunctionName" 50.Fa "int nArg" 51.Fa "int eTextRep" 52.Fa "void *pApp" 53.Fa "void (*xStep)(sqlite3_context*,int,sqlite3_value**)" 54.Fa "void (*xFinal)(sqlite3_context*)" 55.Fa "void (*xValue)(sqlite3_context*)" 56.Fa "void (*xInverse)(sqlite3_context*,int,sqlite3_value**)" 57.Fa "void(*xDestroy)(void*)" 58.Fc 59.Sh DESCRIPTION 60These functions (collectively known as "function creation routines") 61are used to add SQL functions or aggregates or to redefine the behavior 62of existing SQL functions or aggregates. 63The only differences between the three "sqlite3_create_function*" routines 64are the text encoding expected for the second parameter (the name of 65the function being created) and the presence or absence of a destructor 66callback for the application data pointer. 67Function sqlite3_create_window_function() is similar, but allows the 68user to supply the extra callback functions needed by aggregate window functions. 69.Pp 70The first parameter is the database connection to 71which the SQL function is to be added. 72If an application uses more than one database connection then application-defined 73SQL functions must be added to each database connection separately. 74.Pp 75The second parameter is the name of the SQL function to be created 76or redefined. 77The length of the name is limited to 255 bytes in a UTF-8 representation, 78exclusive of the zero-terminator. 79Note that the name length limit is in UTF-8 bytes, not characters nor 80UTF-16 bytes. 81Any attempt to create a function with a longer name will result in 82SQLITE_MISUSE being returned. 83.Pp 84The third parameter (nArg) is the number of arguments that the SQL 85function or aggregate takes. 86If this parameter is -1, then the SQL function or aggregate may take 87any number of arguments between 0 and the limit set by sqlite3_limit(SQLITE_LIMIT_FUNCTION_ARG). 88If the third parameter is less than -1 or greater than 127 then the 89behavior is undefined. 90.Pp 91The fourth parameter, eTextRep, specifies what text encoding 92this SQL function prefers for its parameters. 93The application should set this parameter to SQLITE_UTF16LE 94if the function implementation invokes 95.Fn sqlite3_value_text16le 96on an input, or SQLITE_UTF16BE if the implementation 97invokes 98.Fn sqlite3_value_text16be 99on an input, or SQLITE_UTF16 if 100.Fn sqlite3_value_text16 101is used, or SQLITE_UTF8 otherwise. 102The same SQL function may be registered multiple times using different 103preferred text encodings, with different implementations for each encoding. 104When multiple implementations of the same function are available, SQLite 105will pick the one that involves the least amount of data conversion. 106.Pp 107The fourth parameter may optionally be ORed with SQLITE_DETERMINISTIC 108to signal that the function will always return the same result given 109the same inputs within a single SQL statement. 110Most SQL functions are deterministic. 111The built-in 112.Fn random 113SQL function is an example of a function that is not deterministic. 114The SQLite query planner is able to perform additional optimizations 115on deterministic functions, so use of the SQLITE_DETERMINISTIC 116flag is recommended where possible. 117.Pp 118The fourth parameter may also optionally include the SQLITE_DIRECTONLY 119flag, which if present prevents the function from being invoked from 120within VIEWs, TRIGGERs, CHECK constraints, generated column expressions, 121index expressions, or the WHERE clause of partial indexes. 122.Pp 123For best security, the SQLITE_DIRECTONLY flag is recommended 124for all application-defined SQL functions that do not need to be used 125inside of triggers, view, CHECK constraints, or other elements of the 126database schema. 127This flags is especially recommended for SQL functions that have side 128effects or reveal internal application state. 129Without this flag, an attacker might be able to modify the schema of 130a database file to include invocations of the function with parameters 131chosen by the attacker, which the application will then execute when 132the database file is opened and read. 133.Pp 134The fifth parameter is an arbitrary pointer. 135The implementation of the function can gain access to this pointer 136using 137.Fn sqlite3_user_data . 138The sixth, seventh and eighth parameters passed to the three "sqlite3_create_function*" 139functions, xFunc, xStep and xFinal, are pointers to C-language functions 140that implement the SQL function or aggregate. 141A scalar SQL function requires an implementation of the xFunc callback 142only; NULL pointers must be passed as the xStep and xFinal parameters. 143An aggregate SQL function requires an implementation of xStep and xFinal 144and NULL pointer must be passed for xFunc. 145To delete an existing SQL function or aggregate, pass NULL pointers 146for all three function callbacks. 147.Pp 148The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue 149and xInverse) passed to sqlite3_create_window_function are pointers 150to C-language callbacks that implement the new function. 151xStep and xFinal must both be non-NULL. 152xValue and xInverse may either both be NULL, in which case a regular 153aggregate function is created, or must both be non-NULL, in which case 154the new function may be used as either an aggregate or aggregate window 155function. 156More details regarding the implementation of aggregate window functions 157are available here. 158.Pp 159If the final parameter to sqlite3_create_function_v2() or sqlite3_create_window_function() 160is not NULL, then it is destructor for the application data pointer. 161The destructor is invoked when the function is deleted, either by being 162overloaded or when the database connection closes. 163The destructor is also invoked if the call to sqlite3_create_function_v2() 164fails. 165When the destructor callback is invoked, it is passed a single argument 166which is a copy of the application data pointer which was the fifth 167parameter to sqlite3_create_function_v2(). 168.Pp 169It is permitted to register multiple implementations of the same functions 170with the same name but with either differing numbers of arguments or 171differing preferred text encodings. 172SQLite will use the implementation that most closely matches the way 173in which the SQL function is used. 174A function implementation with a non-negative nArg parameter is a better 175match than a function implementation with a negative nArg. 176A function where the preferred text encoding matches the database encoding 177is a better match than a function where the encoding is different. 178A function where the encoding difference is between UTF16le and UTF16be 179is a closer match than a function where the encoding difference is 180between UTF8 and UTF16. 181.Pp 182Built-in functions may be overloaded by new application-defined functions. 183.Pp 184An application-defined function is permitted to call other SQLite interfaces. 185However, such calls must not close the database connection nor finalize 186or reset the prepared statement in which the function is running. 187.Sh IMPLEMENTATION NOTES 188These declarations were extracted from the 189interface documentation at line 5333. 190.Bd -literal 191SQLITE_API int sqlite3_create_function( 192 sqlite3 *db, 193 const char *zFunctionName, 194 int nArg, 195 int eTextRep, 196 void *pApp, 197 void (*xFunc)(sqlite3_context*,int,sqlite3_value**), 198 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 199 void (*xFinal)(sqlite3_context*) 200); 201SQLITE_API int sqlite3_create_function16( 202 sqlite3 *db, 203 const void *zFunctionName, 204 int nArg, 205 int eTextRep, 206 void *pApp, 207 void (*xFunc)(sqlite3_context*,int,sqlite3_value**), 208 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 209 void (*xFinal)(sqlite3_context*) 210); 211SQLITE_API int sqlite3_create_function_v2( 212 sqlite3 *db, 213 const char *zFunctionName, 214 int nArg, 215 int eTextRep, 216 void *pApp, 217 void (*xFunc)(sqlite3_context*,int,sqlite3_value**), 218 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 219 void (*xFinal)(sqlite3_context*), 220 void(*xDestroy)(void*) 221); 222SQLITE_API int sqlite3_create_window_function( 223 sqlite3 *db, 224 const char *zFunctionName, 225 int nArg, 226 int eTextRep, 227 void *pApp, 228 void (*xStep)(sqlite3_context*,int,sqlite3_value**), 229 void (*xFinal)(sqlite3_context*), 230 void (*xValue)(sqlite3_context*), 231 void (*xInverse)(sqlite3_context*,int,sqlite3_value**), 232 void(*xDestroy)(void*) 233); 234.Ed 235.Sh SEE ALSO 236.Xr sqlite3 3 , 237.Xr sqlite3_limit 3 , 238.Xr sqlite3_user_data 3 , 239.Xr sqlite3_value_blob 3 , 240.Xr SQLITE_DETERMINISTIC 3 , 241.Xr SQLITE_LIMIT_LENGTH 3 , 242.Xr SQLITE_OK 3 , 243.Xr SQLITE_UTF8 3 244