SQLite Forum

Request: stored functions/procedures
Login

Request: stored functions/procedures

(1) By Ron Aaron (ronaaron) on 2020-06-10 05:10:56 [link]

Please add 'stored functions and procedures'. 

Yes, you can create an additional function in C, but that requires changing C code etc, as opposed to adding some SQL to a database.

Since 'triggers' are essentially stored functions, it *should* be doable to add functions.

To make them useful, you would also need to have user-defined variables.  Something like what I can do with MariaDB:

    CREATE FUNCTION joe(p1 CHAR(8)) RETURNS INTEGER
    BEGIN
      SELECT NULL into @result;
      ... SQL stuff ...
      RETURN @result;
    END;

I'm finding that feature (of MariaDB) incredibly useful and would like to see it adopted in SQLite as well, if possible

(2) By Warren Young (wyoung) on 2020-06-10 12:49:59 in reply to 1 [link]

> 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

(3) By Decker (d3x0r) on 2020-06-10 15:11:45 in reply to 1 [link]

Besides all of the other things already said, what flavor of script language would you propose; every RDBMS out there has it own dialect, and it's not like you can just take your procedures and put them anywhere else. This would become yet another cog that we can't get updates and support for.

(4) By Warren Young (wyoung) on 2020-06-10 15:15:43 in reply to 3 [link]

> every RDBMS out there has it own dialect...

...and as far as I can tell, *none* of them are beloved. Who's getting up for work and going, "My goodness I can't wait to write some PL/SQL today!"

(5.1) By Ryan Smith (cuz) on 2020-06-10 15:34:09 edited from 5.0 in reply to 4 [link]

> Who's getting up for work and going, "My goodness I can't wait to write some PL/SQL today!"

Fair point....  But then, which language *do* you get up in the morning for, with that song in your heart?

(6) By Warren Young (wyoung) on 2020-06-10 17:06:09 in reply to 5.1 [link]

> which language do you get up in the morning for, with that song in your heart?

Usually the one I've learned most recently. :)

To say more gets us into a pointless advocacy argument, so instead, let us consider languages which:

* many programmers enjoy using
* are embeddable into a C/C++ host program
* are small enough to add to a host that otherwise has no use for it (i.e. not Node.js!)
* have an "eval" facility, so we can compile and run SQLite TEXT data at runtime
* have a SQLite binding

There's a fairly small set of plausible candidates for the system I suggest in my first post above:

* [Python](https://docs.python.org/3/extending/embedding.html)
* [Lua](https://www.lua.org/manual/5.3/manual.html#4)
* [Tcl](https://wiki.tcl-lang.org/page/How+to+embed+Tcl+in+C+applications) / [Jim](http://jim.tcl.tk/) / [TH1](https://www.fossil-scm.org/index.html/doc/trunk/www/th1.md)
* [Perl](https://perldoc.perl.org/perlembed.html)
* [Ruby](https://silverhammermba.github.io/emberb/embed/)

There are doubtless others. Members of the Church of Lambda the Unchained could pick Guile or Racket or whatever. You could probably do this with Erlang, too. And so on.

(7) By Ron Aaron (ronaaron) on 2020-06-10 17:09:06 in reply to 2 [link]

Fair enough. I do already do that in 8th: you can write extensions which call into 8th code, which as you point out, can call back to SQL. There's even more than one flavor of these extensions.

But...

You could make the same argument against 'triggers': why not just write the code for them in your own app rather than making the SQL engine do the work? There's nothing special about triggers except that they make referential integrity easier to enforce.

(8) By Warren Young (wyoung) on 2020-06-10 17:26:35 in reply to 7

> There's nothing special about triggers except that they make referential integrity easier to enforce.

That ignores race conditions in concurrent update scenarios. If that matters to your app, then triggers are the *only* way to do certain things safely in most SQL DBMSes, because it's the only way to make certain updates happen automatically *inside* the transaction.

SQLite also offers [commit hooks](https://sqlite.org/c3ref/commit_hook.html), which have much the same potential use cases, and more. Maybe that would serve the OP's needs, in fact.

(9) By anonymous on 2020-06-11 09:45:13 in reply to 6 [link]

You're forgetting the most popular language in the world... i.e. JavsScript.

There are very good lightweight and fast pure-C implementations designed for embedding:

- [QuickJS](https://bellard.org/quickjs/)
- [Duktape](https://duktape.org/) (has amalgamation)

The glue with SQLite needs to be written though.

QuickJS is possibly the fastest non-JIT'd such embedded C-based JS engine,  
coming from a well know OSS contributor.

(10) By L Carl (lcarlp) on 2020-06-11 13:53:54 in reply to 4 [link]

Ha!  I wrote many thousands of lines of PL/SQL code in my career and I might have had a morning like that.  It’s a generally sensible language, based on Ada.  Beloved?  Maybe not, but I liked it better than a lot of the other languages I’ve used.  

I mostly agree with all the other comments in this thread.  However, when using Oracle, I appreciated being able to package procedural code in the DBMS and a chief motivation was security and reliability.  It’s nice to be able to guarantee that all updates to a given object, for example, are done by a particular procedure that also implements certain guarantees.  I developed more than one system where almost all the logic was inside the DBMS.  The only other code was things like shell scripts... which are hard to avoid.  So, in those systems I never had to deal with another language like C or Python.  Fewer moving parts.

And I could implement a new function with a simple create statement.  If I could do that in SQLite, it would make life easier.  But there are a lot of caveats....

(11.1) By jose isaias cabrera (jicman) on 2020-06-11 14:35:04 edited from 11.0 in reply to 5.1 [link]

>>Who's getting up for work and going, "My goodness I can't wait to write some PL/SQL today!"

>Fair point.... But then, which language do you get up in the morning for, with that song in your heart?

This is funny. :-) Thanks.

(12) By anonymous on 2020-06-12 19:26:19 in reply to 1 [link]

I don't think functions need to contain entire blocks of SQL codes.

For a scalar function, possibly can use a syntax like:
```
CREATE FUNCTION name(argument_list) AS expr;
```
For a aggregate function (including window functions), possibly can use a syntax like:
```
CREATE AGGREGATE FUNCTION name(argument_list) AS (select_stmt);
```
If you write `AGGREGATE` where a table name is expected in the definition of a aggregate function, then it refers to the data that has been accumulated by the aggregate function. There should also be some way of accessing the row number; this is mainly relevant for window functions, although if it is fixed to allow `ORDER BY` for non-window aggregate functions too (which I think it should allow), then it will be usable with that too.

For procedures, I think that `INSTEAD OF` triggers on views are mostly good enough, although it might help to add:

* Allow `WITH` clauses at the beginning of statements in a trigger program.

* Allow `INSERT INTO` ... `DEFAULT VALUES` inside of a trigger program.

* Allow qualified table names in temporary triggers.

* Add a `FOR` block. The syntax is the same as a `WITH` clause, but with the word `FOR` instead of `WITH`, and it would be followed by `BEGIN`, and then followed by any number of statements and then `END FOR;` at the end.

* Add a `TRY` block, to catch constraint errors resulting from SQL codes inside of the block (whether due to actual table constraints or due to a function or virtual table returning `SQLITE_CONSTRAINT`).

For the `CREATE VIEW` command, allow the list of column names to include data types (which may include `HIDDEN` to be like hidden columns in virtual tables) and default values. (There is already a way to use virtual tables together with joins to extract constraints and use the constrained values in an expression in a view; look for the message elsewhere in the forum and/or mailing list.)

(13) By Warren MacEvoy (wmacevoy) on 2023-06-02 15:54:29 in reply to 9 [link]

+1 for [QuickJS](https://bellard.org/quickjs/).  The datatypes supported for quickjs (bigint, bigdecimal, bigfloat) would be very handy data types in sqlite.

Operating on correct data for your application space, independent of which language you were using sql lite from would be amazing.

It also provides a regex library which would be a nice feature as well.