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