7.9.33. SQL-32 — User-defined SQL scalar functions

SQL is fluent at filtering and grouping but speaks a small built-in vocabulary. When an expression doesn’t fit (a custom hash, a domain-specific scoring formula, a string normalizer), register_function binds an ordinary daslang function as a SQL scalar so it can be used inside any expression — WHERE, projection, ORDER BY, indexes, view bodies, anywhere a built-in scalar would go.

7.9.33.1. The contract

db |> register_function(name : string, fn : @@<...>;
                        deterministic : bool = false;
                        directonly    : bool = false)

name — the SQL identifier the function will be invoked under. Re-registering the same name replaces the prior binding.
fn — a daslang function pointer (@@ form). The macro inspects the pointer’s signature at compile time, derives the per-arg + return type tags, and ships them to the C++ trampoline.
deterministic = true — same inputs always yield the same output. Required to use the fn inside CREATE INDEX ... ON tbl(myfn(col)) or in a generated column.
directonly = true — the fn cannot be called from triggers, views, or CHECK(...) constraints. Useful to lock down side-effecting helpers that should run only from application SQL.

Lifetime. Registration is per-connection. The function is available until the connection closes (or until DROP FUNCTION if exposed in a future chunk).

NULL handling. If any argument is SQL NULL, the trampoline emits NULL without invoking the daslang function — the standard SQL behavior of built-in scalars (abs(NULL) -> NULL, length(NULL) -> NULL). Explicit Option<T> arg types for in-function NULL handling are planned but not yet shipped.

Panic recovery. A panic inside the daslang function is caught and surfaced to SQLite as an error on that statement; try_query returns Err carrying the panic message. The connection itself is unaffected and the next statement runs normally.

7.9.33.2. Supported types

register_function maps daslang scalar types to SQLite cells:

daslang type	SQLite cell
`int` / `int64` / `bool`	INTEGER
`float` / `double`	REAL
`string`	TEXT

Unsupported types (structs, arrays, pointers, lambdas, classes) fail at compile time with a per-position diagnostic. Up to 4 arguments are supported in v1.

7.9.33.3. End-to-end

A scoring formula, a string hasher, a 2-D distance: three real-shaped SQL helpers, all marked deterministic so SQLite is allowed to factor them out of inner loops or use them as the indexed expression in a CREATE INDEX.

require sqlite/sqlite_boost
require sqlite/sqlite_linq
require math

def damage_formula(base : float; armor : int; mult : float) : float {
    return base * (1.0f - float(armor) / 100.0f) * mult
}

def short_hash(s : string) : int64 {
    var h = 1469598103934665603l
    for (c in s) {
        h = (h ^ int64(c)) * 1099511628211l
    }
    return h
}

def euclid(x1 : float; y1 : float; x2 : float; y2 : float) : float {
    let dx = x2 - x1
    let dy = y2 - y1
    return sqrt(dx * dx + dy * dy)
}

[sql_table(name = "Enemies")]
struct Enemy {
    @sql_primary_key Id : int
    Name : string
    Hp : float
    Armor : int
}

[export]
def main {
    with_sqlite(":memory:") $(db) {
        db |> create_table(type<Enemy>)
        // ... insert rows ...

        db |> register_function("damage_formula", @@damage_formula, true)
        db |> register_function("short_hash",     @@short_hash,     true)
        db |> register_function("euclid",         @@euclid,         true)
    }
}

Once registered, the function is just another SQL scalar:

Inside a projection (per-row SELECT result) —

let dmg <- (db |> try_run_select(
    "SELECT damage_formula(100.0, Armor, 1.5) FROM Enemies ORDER BY Id",
    $(var stmt : sqlite3_stmt?) {},
    $(stmt : sqlite3_stmt?) : float {
        return float(sqlite3_column_double(stmt, 0))
    })) |> unwrap

Inside a WHERE clause (filter predicate) —

let weak_count : int = db |> query_scalar(
    "SELECT COUNT(*) FROM Enemies WHERE damage_formula(100.0, Armor, 1.5) > 50",
    type<int>)

Inside ORDER BY (sort key) —

let first_by_hash = db |> query_scalar(
    "SELECT Name FROM Enemies ORDER BY short_hash(Name) LIMIT 1",
    type<string>)

Inside an aggregate (multi-arg numeric fn) —

let total = db |> query_scalar(
    "SELECT SUM(euclid(StartX, StartY, EndX, EndY)) FROM Trips",
    type<double>)

7.9.33.4. Auto-registration with `[sql_function]`

The manual register_function call is per-connection: open a second DB and you have to remember to repeat the call. The [sql_function] annotation removes that ceremony and makes the function visible inside _sql(...) chain analysis, so it can be used as a SQL predicate or projection just like a built-in scalar.

[sql_function]
def normalize(s : string) : string => s |> to_upper

[sql_function(deterministic=true)]
def score(hp : float; armor : int) : double {
    return double(hp) * (1.0lf - double(armor) / 100.0lf)
}

[sql_function(name="event_id")]
def sql_event(tag : string) : int { ... }

The annotation does two things at compile time:

Auto-registration on every open. Each [sql_function] adds a thunk to a global registry; open_sqlite (and with_sqlite, try_open_sqlite) call register_function against the new connection automatically. Every opened DB sees every tagged function.
Chain-analyzer visibility. Inside _sql(...), calls to a [sql_function]-tagged function are emitted as <name>(args...) SQL — SQLite calls the registered UDF in the query engine, instead of falling through to a per-row daslang bind.
```
let strong <- _sql(db |> select_from(type<Enemy>)
    |> _where(score(_.Hp, _.Armor) >= 100.0lf)
    |> _to_array)
```
Emits: SELECT ... FROM "Enemies" WHERE score("Hp", "Armor") >= ?.

Optional annotation args (all default sensibly):

name — override the SQL-side identifier (default: the daslang function name). Use when you want the daslang code to read like daslang but the SQL to read like SQL — e.g. sql_event registers as event_id above.
deterministic — same as register_function’s positional flag (default false). Set true for indexable / generated-column use.
directonly — same as register_function’s positional flag (default false). Locks the function out of triggers / views / CHECK constraints.

Manual ``register_function`` still works — it’s the right tool when you only need the function on a specific connection, when the function isn’t a fit for chain-analyzer use, or when you’re managing UDFs by hand. [sql_function] is the right tool when the function should be ambient SQL and visible to _sql chains.

7.9.33.5. Provider-specific

register_function is SQLite-provider-specific. Other backends have their own user-defined-function registration rails (PostgreSQL’s CREATE FUNCTION with PL/pgSQL, MySQL’s UDF interface, MSSQL’s CREATE FUNCTION + assemblies). A future cross-provider abstraction would have to round-trip across all of them; for now, the daslang function lives behind sqlite/sqlite_boost.

7.9.33.6. Deferred

Aggregate functions (sqlite3_create_function with step + finalize callbacks). Useful for custom GROUP BY aggregates — MEDIAN, STDEV, percentile families. Defer until a real consumer asks.
Window functions (sqlite3_create_window_function). Specialized enough to wait for a real use case.
Explicit Option<T> arg types. Today the trampoline short-circuits NULL arguments; an explicit Option<T> arg would let the function decide what NULL means. Planned.
Variadic + > 4 args. v1 caps at 4 positional args. Lift the cap when something real wants it.