SQLite Forum

Request: stored functions/procedures

Request: stored functions/procedures

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

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:

  SELECT NULL into @result;
  ... SQL stuff ...
  RETURN @result;

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] [source]

that requires changing C code

Not "changing:" writing. You can create a loadable extension 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, six orders of magnitude faster. It is therefore fast to make hundreds of individual queries.

...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!

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

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.


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 [link] [source]

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, which have much the same potential use cases, and more. Maybe that would serve the OP's needs, in fact.

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

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] [source]

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] [source]

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] [source]

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:

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.

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

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:

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.

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

+1 for 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.

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

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.

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

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....

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

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.)