74

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

If so, how? What is the syntax?

Thanks

Andrew Bullock
  • 36,616
  • 34
  • 155
  • 231

3 Answers3

83

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:

Bernardo Ramos
  • 4,048
  • 30
  • 28
Roland Bouman
  • 31,125
  • 6
  • 66
  • 67
  • 6
    @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
8

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.

donnadulcinea
  • 1,854
  • 2
  • 25
  • 37
  • Note that: "Custom SQL functions created in SQLiteStudio exist only in context of SQLiteStudio. Functions are not persisted in database, it's just not how SQLite databases work. If you want to use some function in your own application, you have to register that function in SQLite from your application." https://github.com/pawelsalawa/sqlitestudio/wiki/User_Manual#custom-sql-functions – chrismarx Jul 21 '23 at 14:46
5

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
Anton Zhiyanov
  • 134
  • 1
  • 3