How to create custom functions in SQLite

Question

Can you create functions in SQLite like you can in MSSQL?

If so, how? What is the syntax?

Thanks

score 75 · Accepted Answer · edited Apr 06 '21 at 17:27

75

SQLite does not have a stored function/stored procedure language. So CREATE FUNCTION does not work. What you can do though is map functions from a c library to SQL functions (user-defined functions). To do that, use SQLite's C API (see: http://www.sqlite.org/c3ref/create_function.html)

If you're not using the C API, your wrapper API may define something that allows you access to this feature, see for example:

PHP sqlite_create_function() (http://www.php.net/manual/en/function.sqlite-create-function.php)
Python sqlite3.create_function() (http://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function)
Perl $dbh->sqlite_create_function($name,$argc,$code_ref,$flags) (https://metacpan.org/pod/DBD::SQLite#$dbh-%3Esqlite_create_function(-$name,-$argc,-$code_ref,-$flags-))

edited Apr 06 '21 at 17:27

Bernardo Ramos

3,352
26
25

answered Jan 21 '10 at 11:50

Roland Bouman

29,997
6
65
67

5

@Andrew: Since you mention MSSQL, you might be interested to know that SQLite UDFs are easy to implement in any CLR language using the System.Data.SQLite provider for .NET. http://sqlite.phxsoftware.com/ – Tim Sep 16 '11 at 15:44
4

If you map a user-defined function as you mentioned in this answer, does it retain in the sqlite database or do you have to map it every time you load the db? – ThinkBonobo Feb 06 '14 at 15:21
3

Good question. I think you might have to re-map. – Roland Bouman Feb 15 '14 at 15:56
2

This comment helped me in trying to figure out how to actually write the UDF: http://stackoverflow.com/questions/7867099/how-to-create-a-user-defined-function-in-sqlite – dvntehn00bz Mar 01 '14 at 17:19
7

In case of Python + SQLAlchemy, you can use something like `engine.connect().connection.create_function()` to access the native `create_function()` method of sqlite. Linke: http://docs.sqlalchemy.org/en/latest/core/connections.html#working-with-raw-dbapi-connections – Mahdi Jul 04 '16 at 11:15
1

for c# see this blog: https://www.bricelam.net/2017/08/22/sqlite-efcore-udf-all-the-things.html – alhpe Oct 12 '18 at 00:52
2

what about golang? – Richard Mar 06 '20 at 13:27

score 6 · Answer 2 · answered Mar 08 '18 at 06:21

This could be useful to many: in SQLiteStudio it is possible to define new functions and collations easily from interface through a sql built-in plugin for example.

https://github.com/pawelsalawa/sqlitestudio/wiki/Official_plugins#sql-built-in

Through the function editor.

How to create custom functions in SQLite

2 Answers2

Linked