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. You may wish you had variables, but you can create a one-row temporary table for those. Working around the lack of functions is harder, but here's an example of how you can do it in extremis

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

One thought on “Pretending that SQLite has Stored Procedures and Functions”

Leave a Reply

Your email address will not be published. Required fields are marked *