User-defined functions

Article
09/15/2021

Most databases have a procedural dialect of SQL that you can use to define your own functions. SQLite however, runs in-process with your app. Instead of having to learn a new dialect of SQL, you can just use the programming language of your app.

Scalar functions

Scalar functions return a single, scalar value for each row in a query. Define new scalar functions and override the built-in ones using CreateFunction.

See Data types for a list of supported parameter and return types for the func argument.

Specifying the state argument will pass that value into every invocation of the function. Use this to avoid closures.

Specify isDeterministic if your function is deterministic to allow SQLite to use additional optimizations when compiling queries.

The following example shows how to add a scalar function to calculate the radius of a cylinder.

connection.CreateFunction(
    "volume",
    (double radius, double height)
        => Math.PI * Math.Pow(radius, 2) * height);

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT name,
           volume(radius, height) AS volume
    FROM cylinder
    ORDER BY volume DESC
";

Operators

The following SQLite operators are implemented by corresponding scalar functions. Defining these scalar functions in your app will override the behavior of these operators.

Operator	Function
X GLOB Y	glob(Y, X)
X LIKE Y	like(Y, X)
X LIKE Y ESCAPE Z	like(Y, X, Z)
X MATCH Y	match(Y, X)
X REGEXP Y	regexp(Y, X)

The following example shows how to define the regexp function to enable its corresponding operator. SQLite doesn't include a default implementation of the regexp function.

connection.CreateFunction(
    "regexp",
    (string pattern, string input)
        => Regex.IsMatch(input, pattern));

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT count()
    FROM user
    WHERE bio REGEXP '\w\. {2,}\w'
";
var count = command.ExecuteScalar();

Aggregate functions

Aggregate functions return a single, aggregated value for all the rows in a query. Define and override aggregate functions using CreateAggregate.

The seed argument specifies the initial state of the context. Use this to avoid closures also.

The func argument is invoked once per row. Use the context to accumulate a final result. Return the context. This pattern allows the context to be a value type or immutable.

If no resultSelector is specified, the final state of the context is used as the result. This can simplify the definition of functions like sum and count that only need to increment a number each row and return it.

Specify resultSelector to calculate the final result from the context after iterating through all the rows.

See Data types for a list of supported parameter types for the func argument and return types for resultSelector.

If your function is deterministic, specify isDeterministic to allow SQLite to use additional optimizations when compiling queries.

The following example defines an aggregate function to calculate the standard deviation of a column.

connection.CreateAggregate(
    "stdev",

    // A tuple to maintain context between rows
    (Count: 0, Sum: 0.0, SumOfSquares: 0.0),

    // This is called for each row
    ((int Count, double Sum, double SumOfSquares) context, double value) =>
    {
        context.Count++;
        context.Sum += value;
        context.SumOfSquares += value * value;

        return context;
    },

    // This is called to get the final result
    context =>
    {
        var variance = context.SumOfSquares - context.Sum * context.Sum / context.Count;

        return Math.Sqrt(variance / context.Count);
    });

var command = connection.CreateCommand();
command.CommandText =
@"
    SELECT stdev(gpa)
    FROM student
";
var stdDev = command.ExecuteScalar();

Errors

If a user-defined function throws an exception, the message is returned to SQLite. SQLite will then raise an error and Microsoft.Data.Sqlite will throw a SqliteException. For more information, see Database errors.

By default, the error SQLite error code will be SQLITE_ERROR (or 1). You can, however, change it by throwing a SqliteException in your function with the desired SqliteErrorCode specified.

Debugging

SQLite calls your implementation directly. This lets you add breakpoints that trigger while SQLite is evaluating queries. The full .NET debugging experience is available to help you create your user-defined functions.

Share via