SQLite application defined function in Go, combined with generated column

go.mod

module sqlite-generated-columns

go 1.22.3

require modernc.org/sqlite v1.29.9

require (
	github.com/dustin/go-humanize v1.0.1 // indirect
	github.com/google/uuid v1.6.0 // indirect
	github.com/hashicorp/golang-lru/v2 v2.0.7 // indirect
	github.com/mattn/go-isatty v0.0.20 // indirect
	github.com/ncruces/go-strftime v0.1.9 // indirect
	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
	golang.org/x/sys v0.19.0 // indirect
	modernc.org/gc/v3 v3.0.0-20240107210532-573471604cb6 // indirect
	modernc.org/libc v1.49.3 // indirect
	modernc.org/mathutil v1.6.0 // indirect
	modernc.org/memory v1.8.0 // indirect
	modernc.org/strutil v1.2.0 // indirect
	modernc.org/token v1.1.0 // indirect
)

main.go

package main

import (
	"database/sql"
	"database/sql/driver"
	"fmt"
	"log"

	"modernc.org/sqlite"
)

func main() {
	log.SetFlags(0)
	if err := run(); err != nil {
		log.Fatal(err)
	}
}

func run() error {
	if err := sqlite.RegisterFunction("myFunc", &sqlite.FunctionImpl{
		NArgs:         1,
		Deterministic: true,
		Scalar:        myFunc,
	}); err != nil {
		return fmt.Errorf("registering function: %w", err)
	}
	db, err := sql.Open("sqlite", "db.sqlite")
	if err != nil {
		return err
	}
	defer db.Close()
	if err := initSchema(db); err != nil {
		return fmt.Errorf("schema init: %w", err)
	}
	for i := range 3 {
		if _, err := db.Exec(`insert into t(val) values(?)`, i); err != nil {
			return fmt.Errorf("inserting %d: %w", i, err)
		}
	}
	rows, err := db.Query(`select val, calc from t`)
	if err != nil {
		return fmt.Errorf("querying table: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var val, calc int64
		if err := rows.Scan(&val, &calc); err != nil {
			return fmt.Errorf("scanning values: %w", err)
		}
		fmt.Printf("val: %d, calc: %v\n", val, calc)
	}
	return rows.Err()
}

func initSchema(db *sql.DB) error {
	_, err := db.Exec(`create table if not exists t(
		val int not null,
		calc int as (myFunc(val))
	) strict`)
	return err
}

func myFunc(_ *sqlite.FunctionContext, args []driver.Value) (driver.Value, error) {
	if len(args) != 1 {
		return nil, fmt.Errorf("myFunc supports 1 argument, got %d", len(args))
	}
	v, ok := args[0].(int64)
	if !ok {
		return nil, fmt.Errorf("myFunc supports arguments of int64 type, got %T", args[0])
	}
	return v + 42, nil
}