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

Answer 1

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() (Archived with Wayback Machine (archive.org))
• PHP SQLite3::createFunction (Manual page from php.net)
• Python sqlite3.create_function() (Documentation from docs.python.org)
• Perl $dbh->sqlite_create_function($name,$argc,$code_ref,$flags) (Module documentation from MetaCPAN )

Answer 2

You can write arbitrary functions in SQL with the define extension:

-- define a function to sum the numbers 1..n
select define('sumn', ':n * (:n + 1) / 2');

-- use it as a regular function
select sumn(3);
6
select sumn(5);
15

Answer 3

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.