Article Index
SQLite Tutorial
Logging All Inserts, Updates, and Deletes
UTC and Localtime
The ATTACH Command
The Power of the Sign Function
Creating a Permanent Sign Function
Spreadsheet Format to Normalized Data
C and C API
A C Program -- Building a Class to Do the Work
Defining SQLite User Functions
Aggregate Functions
Reading Images (Blob data)

Defining SQLite User Functions

There are two types of functions, aggregate functions and simple functions. Simple functions like sign(), which was created above, can be used in any expression. Aggregate functions like "avg()" can only be used in the select statement. Some functions like "min" and "max" can be defined as both. min() with 1 argument is an aggregate function, whereas min() with an unlimited number of arguments is a simple function.

Here is an example which illustrates the difference:

$ sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table junk (a integer);
sqlite> insert into junk (a) values (1);
sqlite> insert into junk (a) values (2);
sqlite> insert into junk (a) values (3);
sqlite> select * from junk;
1
2
3

sqlite> select * from junk where a=min(1,2,3,4,5);
1
sqlite> select * from junk where a=min(1);
SQL error: misuse of aggregate function min()
sqlite>

Note above that the min() function, with only one variable, is an aggregate function. Since it is only an aggregate function, it cannot be used after the where clause. An aggregate function can only be used after the select clause as follows:

sqlite> select min(a) from junk
1

If you add a second argument, you're calling the simple function. Note below that each row is compared to 2.3. Look closely; there is a subtle but important difference here.

sqlite> select min(a,2.3) from junk
2
2.3
1

Creating a User-defined Sign Function: msign

Below is an example of the sign function. It is called msignFunc so as not to interfere with the permanent sign function that was created earlier.

void msignFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
assert( argc==1 );
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
long long int iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
sqlite3_result_null(context);
break;
}
default: {
double rVal = sqlite3_value_double(argv[0]);
rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
sqlite3_result_double(context, rVal);
break;
}
}
}

This function is initiated as follows:

sqlite3_create_function(db, "msign", 1, SQLITE_UTF8, NULL,
&msignFunc, NULL, NULL));

Note that "msign" is the name of the function in sqlite3. It is the name you would use in a select statement: "select msign(3);". The 1 is the number of arguments. The msign function here only takes one argument. SQLITE_UTF8 is for the text representation. Then, skipping over NULL, &msignFunc is the name of the C function. The last two values must be NULL for a simple function; again, a simple function can be used in any part of the select where clause.