xref: /netbsd-src/external/public-domain/sqlite/man/sqlite3_create_function.3 (revision b9988867a8ad969c45a52aa7628bc932ec98d46b)
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