Pretending that SQLite has Stored Procedures and Functions

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 args or 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:

1. A one-row table for function arguments
2. 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;

And then:

Update Args Set X=22.0, Y=0.00000000000001;
Select * from Exp;
#
# > 3584912846.1315813 # Exp(22) correct to 14 digits

Links

• 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