Debian Codegraph @

www/c3ref/create_function.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="../sqlite.css" rel="stylesheet">
6 <title>Create Or Redefine SQL Functions</title>
7 <!-- path=../ -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="../index.html">
12 <img class="logo" src="../images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="../index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='../about.html'>About</a>
23 <li class='desktoponly'><a href="../docs.html">Documentation</a>
24 <li class='desktoponly'><a href="../download.html">Download</a>
25 <li class='wideonly'><a href='../copyright.html'>License</a>
26 <li class='desktoponly'><a href="../support.html">Support</a>
27 <li class='desktoponly'><a href="../prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='../about.html'>About</a>
35 <li><a href='../docs.html'>Documentation</a>
36 <li><a href='../download.html'>Download</a>
37 <li><a href='../support.html'>Support</a>
38 <li><a href='../prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="../search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <!-- keywords: {function creation routines} sqlite3_create_function sqlite3_create_function16 sqlite3_create_function_v2 sqlite3_create_window_function -->
119 <div class=nosearch>
120 <a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a>
121 <h2>Create Or Redefine SQL Functions</h2>
122 </div>
123 <blockquote><pre>
124 int sqlite3_create_function(
125 sqlite3 *db,
126 const char *zFunctionName,
127 int nArg,
128 int eTextRep,
129 void *pApp,
130 void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
131 void (*xStep)(sqlite3_context*,int,sqlite3_value**),
132 void (*xFinal)(sqlite3_context*)
133 );
134 int sqlite3_create_function16(
135 sqlite3 *db,
136 const void *zFunctionName,
137 int nArg,
138 int eTextRep,
139 void *pApp,
140 void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
141 void (*xStep)(sqlite3_context*,int,sqlite3_value**),
142 void (*xFinal)(sqlite3_context*)
143 );
144 int sqlite3_create_function_v2(
145 sqlite3 *db,
146 const char *zFunctionName,
147 int nArg,
148 int eTextRep,
149 void *pApp,
150 void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
151 void (*xStep)(sqlite3_context*,int,sqlite3_value**),
152 void (*xFinal)(sqlite3_context*),
153 void(*xDestroy)(void*)
154 );
155 int sqlite3_create_window_function(
156 sqlite3 *db,
157 const char *zFunctionName,
158 int nArg,
159 int eTextRep,
160 void *pApp,
161 void (*xStep)(sqlite3_context*,int,sqlite3_value**),
162 void (*xFinal)(sqlite3_context*),
163 void (*xValue)(sqlite3_context*),
164 void (*xInverse)(sqlite3_context*,int,sqlite3_value**),
165 void(*xDestroy)(void*)
166 );
167 </pre></blockquote>
168 <p>
169 These functions (collectively known as "function creation routines")
170 are used to add SQL functions or aggregates or to redefine the behavior
171 of existing SQL functions or aggregates. The only differences between
172 the three "sqlite3_create_function*" routines are the text encoding
173 expected for the second parameter (the name of the function being
174 created) and the presence or absence of a destructor callback for
175 the application data pointer. Function sqlite3_create_window_function()
176 is similar, but allows the user to supply the extra callback functions
177 needed by <a href="../windowfunctions.html#aggwinfunc">aggregate window functions</a>.</p>
178
179 <p>The first parameter is the <a href="../c3ref/sqlite3.html">database connection</a> to which the SQL
180 function is to be added. If an application uses more than one database
181 connection then application-defined SQL functions must be added
182 to each database connection separately.</p>
183
184 <p>The second parameter is the name of the SQL function to be created or
185 redefined. The length of the name is limited to 255 bytes in a UTF-8
186 representation, exclusive of the zero-terminator. Note that the name
187 length limit is in UTF-8 bytes, not characters nor UTF-16 bytes.
188 Any attempt to create a function with a longer name
189 will result in <a href="../rescode.html#misuse">SQLITE_MISUSE</a> being returned.</p>
190
191 <p>The third parameter (nArg)
192 is the number of arguments that the SQL function or
193 aggregate takes. If this parameter is -1, then the SQL function or
194 aggregate may take any number of arguments between 0 and the limit
195 set by <a href="../c3ref/limit.html">sqlite3_limit</a>(<a href="../c3ref/c_limit_attached.html#sqlitelimitfunctionarg">SQLITE_LIMIT_FUNCTION_ARG</a>). If the third
196 parameter is less than -1 or greater than 127 then the behavior is
197 undefined.</p>
198
199 <p>The fourth parameter, eTextRep, specifies what
200 <a href="../c3ref/c_any.html#sqliteutf8">text encoding</a> this SQL function prefers for
201 its parameters. The application should set this parameter to
202 <a href="../c3ref/c_any.html#sqliteutf16le">SQLITE_UTF16LE</a> if the function implementation invokes
203 <a href="../c3ref/value_blob.html">sqlite3_value_text16le()</a> on an input, or <a href="../c3ref/c_any.html#sqliteutf16be">SQLITE_UTF16BE</a> if the
204 implementation invokes <a href="../c3ref/value_blob.html">sqlite3_value_text16be()</a> on an input, or
205 <a href="../c3ref/c_any.html#sqliteutf16">SQLITE_UTF16</a> if <a href="../c3ref/value_blob.html">sqlite3_value_text16()</a> is used, or <a href="../c3ref/c_any.html#sqliteutf8">SQLITE_UTF8</a>
206 otherwise. The same SQL function may be registered multiple times using
207 different preferred text encodings, with different implementations for
208 each encoding.
209 When multiple implementations of the same function are available, SQLite
210 will pick the one that involves the least amount of data conversion.</p>
211
212 <p>The fourth parameter may optionally be ORed with <a href="../c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>
213 to signal that the function will always return the same result given
214 the same inputs within a single SQL statement. Most SQL functions are
215 deterministic. The built-in <a href="../lang_corefunc.html#random">random()</a> SQL function is an example of a
216 function that is not deterministic. The SQLite query planner is able to
217 perform additional optimizations on deterministic functions, so use
218 of the <a href="../c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> flag is recommended where possible.</p>
219
220 <p>The fourth parameter may also optionally include the <a href="../c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a>
221 flag, which if present prevents the function from being invoked from
222 within VIEWs, TRIGGERs, CHECK constraints, generated column expressions,
223 index expressions, or the WHERE clause of partial indexes.</p>
224
225 <p>For best security, the <a href="../c3ref/c_deterministic.html#sqlitedirectonly">SQLITE_DIRECTONLY</a> flag is recommended for
226 all application-defined SQL functions that do not need to be
227 used inside of triggers, views, CHECK constraints, or other elements of
228 the database schema. This flag is especially recommended for SQL
229 functions that have side effects or reveal internal application state.
230 Without this flag, an attacker might be able to modify the schema of
231 a database file to include invocations of the function with parameters
232 chosen by the attacker, which the application will then execute when
233 the database file is opened and read.</p>
234
235 <p>The fifth parameter is an arbitrary pointer. The implementation of the
236 function can gain access to this pointer using <a href="../c3ref/user_data.html">sqlite3_user_data()</a>.</p>
237
238 <p>The sixth, seventh and eighth parameters passed to the three
239 "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are
240 pointers to C-language functions that implement the SQL function or
241 aggregate. A scalar SQL function requires an implementation of the xFunc
242 callback only; NULL pointers must be passed as the xStep and xFinal
243 parameters. An aggregate SQL function requires an implementation of xStep
244 and xFinal and NULL pointer must be passed for xFunc. To delete an existing
245 SQL function or aggregate, pass NULL pointers for all three function
246 callbacks.</p>
247
248 <p>The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue
249 and xInverse) passed to sqlite3_create_window_function are pointers to
250 C-language callbacks that implement the new function. xStep and xFinal
251 must both be non-NULL. xValue and xInverse may either both be NULL, in
252 which case a regular aggregate function is created, or must both be
253 non-NULL, in which case the new function may be used as either an aggregate
254 or aggregate window function. More details regarding the implementation
255 of aggregate window functions are
256 <a href="../windowfunctions.html#udfwinfunc">available here</a>.</p>
257
258 <p>If the final parameter to sqlite3_create_function_v2() or
259 sqlite3_create_window_function() is not NULL, then it is the destructor for
260 the application data pointer. The destructor is invoked when the function
261 is deleted, either by being overloaded or when the database connection
262 closes. The destructor is also invoked if the call to
263 sqlite3_create_function_v2() fails. When the destructor callback is
264 invoked, it is passed a single argument which is a copy of the application
265 data pointer which was the fifth parameter to sqlite3_create_function_v2().</p>
266
267 <p>It is permitted to register multiple implementations of the same
268 functions with the same name but with either differing numbers of
269 arguments or differing preferred text encodings. SQLite will use
270 the implementation that most closely matches the way in which the
271 SQL function is used. A function implementation with a non-negative
272 nArg parameter is a better match than a function implementation with
273 a negative nArg. A function where the preferred text encoding
274 matches the database encoding is a better
275 match than a function where the encoding is different.
276 A function where the encoding difference is between UTF16le and UTF16be
277 is a closer match than a function where the encoding difference is
278 between UTF8 and UTF16.</p>
279
280 <p>Built-in functions may be overloaded by new application-defined functions.</p>
281
282 <p>An application-defined function is permitted to call other
283 SQLite interfaces. However, such calls must not
284 close the database connection nor finalize or reset the prepared
285 statement in which the function is running.
286 </p><p>See also lists of
287 <a href="../c3ref/objlist.html">Objects</a>,
288 <a href="../c3ref/constlist.html">Constants</a>, and
289 <a href="../c3ref/funclist.html">Functions</a>.</p>
290