SQLite Forum

Request: stored functions/procedures
> that requires changing C code

Not "changing:" writing. You can create a [loadable extension][1] for this.

> Since 'triggers' are essentially stored functions...

SQL isn't imperative, it's declarative: you say what you want accomplished, not how it must be accomplished. Stored functions flip that, screwing up one of the primary value propositions of SQL, usually doing so with a terrible programming language on top of that.

> I'm finding that feature (of MariaDB) incredibly useful...

A huge reason why all of the major client-server RDBMSes have stored procedures is *because* they are client-server systems. Every round trip costs something on the order of milliseconds purely because of the network overhead, so you have a strong incentive to bundle up a bunch of operations on the server side to amortize that overhead over multiple SQL statements.

With SQLite, database calls are [on the order of single-digit nanoseconds away][1], six orders of magnitude faster. It is therefore [fast to make hundreds of individual queries][3].

> ...as opposed to adding some SQL to a database.

Embed your favorite scripting language into your app, then write the script chunks in that language, store them in the DB, and then execute them.

Since that scripting language almost certainly has a SQLite binding, you could then recursively define SQLite extension functions *in the scripting language*!

[1]: https://sqlite.org/loadext.html
[2]: https://arxiv.org/pdf/1202.2736.pdf
[3]: https://sqlite.org/np1queryprob.html