User-defined functions in SQLite

Most database engines provide a lot of built-in functions. Still, sometimes they are not enough, and people turn to writing their own — user-defined — functions in plain SQL or some SQL-based language (like pl/sql in Oracle or pl/pgsql in Postgres).

SQLite does not support user-defined functions by default. However, you can easily enable them using the sqlean-define extension.

Note. Unlike other DBMS, adding extensions to SQLite is a breeze. Download a file, run one database command — and you are good to go.

Custom functions • Dynamic SQL • Installation and usage

Custom functions

With sqlean-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

42
17
29

List user-defined functions:

.mode box
select * from sqlean_define;

┌──────┬────────┬───────────────────┐
│ name │  type  │       body        │
├──────┼────────┼───────────────────┤
│ sumn │ scalar │ :n * (:n + 1) / 2 │
└──────┴────────┴───────────────────┘

Delete a function:

select undefine('sumn');

ok

There is even a way to return multiple values from a function! (see the extension documentation for details)

Dynamic SQL

To dynamically compose an SQL query and execute it without creating a function, use eval():

select eval('select 10 + 32');
-- 42

Supports any DDL or DML queries:

select eval('create table tmp(value int)');
select eval('insert into tmp(value) values (1), (2), (3)');
select eval('select value from tmp');
select eval('drop table tmp');

1 2 3

Installation and usage

Download the latest release
Use with SQLite command-line interface:

sqlite> .load ./define
sqlite> select define('sumn', ':n * (:n + 1) / 2');
sqlite> select sumn(5);