add user defined functions

[wdduncan]

SQLite, doesn't have user defined functions or stored procedures per se, but many languages do have capabilities to add user defined functions to SQLite. This may be useful for some operations ... not sure which ones yet :)

Here is an example of creating a user defined function in Python: https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function

[cmungall]

cool!

Python3 docs here https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function

This maybe has limited utility if it can only be used by python code operating on the same instance

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

con = sqlite3.connect("db/pato.db")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",))
print(cur.fetchone()[0])

con.close()

$ python func.py
acbd18db4cc2f85cedef654fccc4a4d8

so far so good...

$ sqlite3 db/pato.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> select md5('foo');
Error: no such function: md5

sad face!

[cmungall]

made https://github.com/cmungall/semantic-sql/discussions for this kind of thing