SQLite is marvellous. The fact that it doesn't have SQL syntax for stored procs and functions is usually not a handicap because it has an interface for the consuming application to register functions, which means you get to write your functions in your preferred programming language. Win all round.
But sometimes you do wish you could do it in SQL.
The lack of Stored Procedures is usually ok—you can just use scripts. Variables are easy to do: create a one-row temporary table and call it
var. Working around the lack of Functions seems harder but in fact, you can program functions with Views. You can use CTEs in a View definition, so you can build up complex calculations. And CTEs allow recursion so you have Turing completeness.
As an example, the Exponent function as a View:
Drop Table if Exists Args ; Create Table Args as Select 5.5 as Base, 4 as Exponent ; Drop View If Exists Power; Create View Power As WITH RECURSIVE pow(exponent, exponent_remainder, base, result) as ( --FIRST EXPRESSION SELECT exponent, exponent-1 , base, base FROM Args union all --SECOND EXPRESSION select Args.exponent, pow.exponent_remainder -1, pow.base, pow.result * pow.base from Args join pow on Args.exponent = pow.exponent where pow.exponent_remainder >= 0 ) select pow.result from pow where pow.exponent_remainder = 0;
and now you ‘call the function’ with:
Update Args set Base=2.5, Exponent=5; Select Result from Power;
The elements of the workaround are:
- A one-row table for function arguments
- A view which can refer to the arguments table and do the calculation. Since you can use CTEs to do recursion, you could in principle programming anything this way.
In similar style, here's an Exponential function which lets you specify how many significant digits you want the result to, default to about 7 digits of accuracy. This time we call the Args (X,Y,Z,p4,p5,…)
Drop Table if Exists Args ; Create Table Args as Select 1 as X, 2 as Y, 3 as Z, 4 as p4, 5 as p5, 6 as p6; Drop View If Exists Exp; Create View Exp As WITH RECURSIVE exp1(X, N, term, approx, accuracy ) as ( --FIRST EXPRESSION SELECT X, 1, X, 1+X, Max(Min(Y, 1),0.00000000000000001) FROM Args Union All --SECOND EXPRESSION Select X, N + 1, term * X / (N + 1), approx + term * X / (N + 1), accuracy From exp1 Where term / approx > accuracy Or N <3 ) Select approx as Result From exp1 Order By N Desc Limit 1;
Update Args Set X=22.0, Y=0.00000000000001; Select * from Exp; # # > 3584912846.1315813 # Exp(22) correct to 14 digits
- SQLite CTEs https://sqlite.org/lang_with.html
- Silvano González's answer on https://stackoverflow.com/questions/13190064/how-to-find-power-of-a-number-in-sqlite/13190799#13190124
4 thoughts on “Pretending that SQLite has Stored Procedures and Functions”
A procedure like interface can be achieved without the need for a table, via the use of an instead-of-trigger on a view.
e.g, create a dummy view
[Create View procTest As Select 1 as a, 2 as b;]
where a & b represent the arguments you want to pass in.
CREATE TRIGGER procTest_insert INSTEAD OF INSERT ON procTest
— Do Procedure Logic;
— Access to the input args via the virtual New table
— Can do multiple statements, inserts, updates, deletes etc.
wonderful ideas friends.
what about doing a loop in a trigger?
Yeah, that would probably be neater.