User-defined functions in SQLite

Write functions in plain SQL using the ‘define’ extension.

SQLite does not directly support user-defined functions. Sure, one can write a function in C or Python and register it within SQLite. But not in SQL itself.

Luckily for us, SQLite provides an extension mechanism. One of such extensions — define — allows writing functions in regular SQL.

With define writing a custom function becomes as easy as:

select define('sumn', ':n * (:n + 1) / 2');

And then using it as any built-in function:

select sumn(5);
-- 15

User-defined functions can take multiple parameters and call other functions.

Generate a random N such that a ≤ N ≤ b:

select define('randint', ':a + abs(random()) % (:b - :a + 1)');
select randint(10, 99);
-- 42
select randint(10, 99);
-- 17
select randint(10, 99);
-- 29

List user-defined functions:

select * from sqlean_define;

Delete a function:

select undefine('sumn');

There is even a way to return multiple values from a function!

See the docs for details

Follow @ohmypy on Twitter to keep up with new posts 🚀