1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Create Or Redefine SQL Functions</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(../images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(../images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(../images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(../images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
71
<a href="../index.html">
72
<img class="logo" src="../images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="../about.html">About</a>
83
<a href="../sitemap.html">Sitemap</a>
84
<a href="../docs.html">Documentation</a>
85
<a href="../download.html">Download</a>
86
<a href="../copyright.html">License</a>
87
<a href="../news.html">News</a>
88
<a href="../support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<a href="intro.html"><h2>SQLite C Interface</h2></a><h2>Create Or Redefine SQL Functions</h2><blockquote><pre>int sqlite3_create_function(
122
const char *zFunctionName,
126
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
127
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
128
void (*xFinal)(sqlite3_context*)
130
int sqlite3_create_function16(
132
const void *zFunctionName,
136
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
137
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
138
void (*xFinal)(sqlite3_context*)
140
int sqlite3_create_function_v2(
142
const char *zFunctionName,
146
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
147
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
148
void (*xFinal)(sqlite3_context*),
149
void(*xDestroy)(void*)
151
</pre></blockquote><p>
152
These functions (collectively known as "function creation routines")
153
are used to add SQL functions or aggregates or to redefine the behavior
154
of existing SQL functions or aggregates. The only differences between
155
these routines are the text encoding expected for
156
the second parameter (the name of the function being created)
157
and the presence or absence of a destructor callback for
158
the application data pointer.</p>
160
<p>The first parameter is the <a href="../c3ref/sqlite3.html">database connection</a> to which the SQL
161
function is to be added. If an application uses more than one database
162
connection then application-defined SQL functions must be added
163
to each database connection separately.</p>
165
<p>The second parameter is the name of the SQL function to be created or
166
redefined. The length of the name is limited to 255 bytes in a UTF-8
167
representation, exclusive of the zero-terminator. Note that the name
168
length limit is in UTF-8 bytes, not characters nor UTF-16 bytes.
169
Any attempt to create a function with a longer name
170
will result in <a href="../c3ref/c_abort.html">SQLITE_MISUSE</a> being returned.</p>
172
<p>The third parameter (nArg)
173
is the number of arguments that the SQL function or
174
aggregate takes. If this parameter is -1, then the SQL function or
175
aggregate may take any number of arguments between 0 and the limit
176
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
177
parameter is less than -1 or greater than 127 then the behavior is
180
<p>The fourth parameter, eTextRep, specifies what
181
<a href="../c3ref/c_any.html">text encoding</a> this SQL function prefers for
182
its parameters. Every SQL function implementation must be able to work
183
with UTF-8, UTF-16le, or UTF-16be. But some implementations may be
184
more efficient with one encoding than another. An application may
185
invoke sqlite3_create_function() or sqlite3_create_function16() multiple
186
times with the same function but with different values of eTextRep.
187
When multiple implementations of the same function are available, SQLite
188
will pick the one that involves the least amount of data conversion.
189
If there is only a single implementation which does not care what text
190
encoding is used, then the fourth argument should be <a href="../c3ref/c_any.html">SQLITE_ANY</a>.</p>
192
<p>The fifth parameter is an arbitrary pointer. The implementation of the
193
function can gain access to this pointer using <a href="../c3ref/user_data.html">sqlite3_user_data()</a>.</p>
195
<p>The sixth, seventh and eighth parameters, xFunc, xStep and xFinal, are
196
pointers to C-language functions that implement the SQL function or
197
aggregate. A scalar SQL function requires an implementation of the xFunc
198
callback only; NULL pointers must be passed as the xStep and xFinal
199
parameters. An aggregate SQL function requires an implementation of xStep
200
and xFinal and NULL pointer must be passed for xFunc. To delete an existing
201
SQL function or aggregate, pass NULL pointers for all three function
204
<p>If the ninth parameter to sqlite3_create_function_v2() is not NULL,
205
then it is destructor for the application data pointer.
206
The destructor is invoked when the function is deleted, either by being
207
overloaded or when the database connection closes.
208
The destructor is also invoked if the call to
209
sqlite3_create_function_v2() fails.
210
When the destructor callback of the tenth parameter is invoked, it
211
is passed a single argument which is a copy of the application data
212
pointer which was the fifth parameter to sqlite3_create_function_v2().</p>
214
<p>It is permitted to register multiple implementations of the same
215
functions with the same name but with either differing numbers of
216
arguments or differing preferred text encodings. SQLite will use
217
the implementation that most closely matches the way in which the
218
SQL function is used. A function implementation with a non-negative
219
nArg parameter is a better match than a function implementation with
220
a negative nArg. A function where the preferred text encoding
221
matches the database encoding is a better
222
match than a function where the encoding is different.
223
A function where the encoding difference is between UTF16le and UTF16be
224
is a closer match than a function where the encoding difference is
225
between UTF8 and UTF16.</p>
227
<p>Built-in functions may be overloaded by new application-defined functions.</p>
229
<p>An application-defined function is permitted to call other
230
SQLite interfaces. However, such calls must not
231
close the database connection nor finalize or reset the prepared
232
statement in which the function is running.
233
</p><p>See also lists of
234
<a href="objlist.html">Objects</a>,
235
<a href="constlist.html">Constants</a>, and
236
<a href="funclist.html">Functions</a>.</p>