SQLite Functions and Virtual Tables

SQLite provides functions that enable the user to define functions and virtual tables in C. While the API calls themselves are fairly well documented, I found examples somewhat hard to come by. Furthermore, because there's no central archive of user-written, user-defined SQLite functions, many SQLite users are surely writing and re-writing the same or very similar ones. I thought starting such a repository could be my small contribution to the SQLite project, and a way to illustrate what may be some novel uses for it.

Virtual Tables

Shell file glob expansion
Calls glob(3) on a provided pattern.
Returns column of names matching the pattern.
Directory listing
Calls opendir(3) and readdir(3) to list the filenames in a directory.
CSV file
Parses a comma-separated value file as a table. BSD license.

User-Defined Functions

Regular Expressions
Calls regcomp(3) and regexec(3) on a user-provided pattern for a column or constant
File Metadata
Calls stat(2) and strmode(3) on a filename, displays one field from the stat struct.
These functions are included in readdir.c, discussed above in Virtual Tables.
Math functions
Although the SQLite website doesn't make finding it very easy, there is a Contributor's Page, which includes math and string functions contributed by Liam Healy.

Undocumented requirements

I bumped into two issues writing my first functions.

  1. SQLITE_EXTENSION_INIT1 and SQLITE_EXTENSION_INIT2. These macros define and set the magic sqlite3_api pointer. They are defined in sqlite3ext.h.
  2. The sqlite3_extension_init is called by the load_extension function to load a shared library containing SQLite extensions. It's documented on the Core Functions page in the SQL language definition, but not among the SQLite functions. Recent versions of the sqlite3 interactive shell support this via the .load meta-command, but that command wasn't listed in the manual as of version 3.7.13.


The files provided here compile on NetBSD using BSD make.

A word about the license: except as noted, there is none. As of now, all files provided here were written by me (James K. Lowden) for your edification only. If you're interested in using them or contributing some functions of your own, please contact me.