Creating a custom function for sqlite3

The function

For the purposes of this example, we are going to create a trivial "ifelse" function that takes three arguments. The first is a boolean value. The second argument is the value that will be returned if the boolean evaluates to true (ie: non-zero). The third is the value to be returned if the boolean is false (ie: zero).

These notes are written with reference to sqlite3 3.7.11 running under RedHat linux 5.5 - but will/should work with other linuxes without change. Minor variations are needed on other platforms.

ifelse(condition, true_value, false_value)

example of use..

.load ./mymod.so
select ifelse(gender='M', 'Male', 'Female') from people;

C source code

#include <stdlib.h>
#include "sqlite3.h"

typedef sqlite3_int64 i64;

static void ifelseFunc(sqlite3_context *context, int argc, sqlite3_value **argv) {
        int flag = sqlite3_value_int(argv[0]);
        char *v1 = (char *)sqlite3_value_text(argv[1]);
        char *v2 = (char *)sqlite3_value_text(argv[2]);

        if (flag != 0)
                sqlite3_result_text(context, v1, -1, SQLITE_TRANSIENT);
        else
                sqlite3_result_text(context, v2, -1, SQLITE_TRANSIENT);
}

int sqlite3_extension_init(sqlite3 *db, char **err) {
        sqlite3_create_function( db, "ifelse", 3, SQLITE_ANY, NULL, ifelseFunc, NULL, NULL);
        return 0;
}

Compiling (on linux)

cc --shared -fPIC -I sqlite-autoconf-3071100 mymod.c -o mymod.so
Scroll to Top