Can you create functions in SQLite like you can in MSSQL?
If so, how? What is the syntax?
Thanks
Add a comment
|
3 Answers
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 )
8 Comments
Tim
@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. sqlite.phxsoftware.com
ThinkBonobo
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?
Roland Bouman
Good question. I think you might have to re-map.
dvntehn00bz
This comment helped me in trying to figure out how to actually write the UDF: stackoverflow.com/questions/7867099/…
Mahdi
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: docs.sqlalchemy.org/en/latest/core/… |
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.
1 Comment
chrismarx
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." github.com/pawelsalawa/sqlitestudio/wiki/…
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
2 Comments
Berik
Sqlite does not have the function
define
Anton Zhiyanov
It's an extension.