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