> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Extension API Reference

> VDF API contracts, null handling, buffer sizing, encode/decode conventions, prerun/postrun hooks, and SQL feature compatibility for VillageSQL extensions.

This page is a reference for extension authors. For the step-by-step tutorial,
see [Creating Extensions](/docs/mysql-8.4/0.0.4/create). For custom column types,
see [Creating Custom Types](/docs/mysql-8.4/0.0.4/custom-types).

## VDF Function Contracts

These contracts govern how VDF implementation functions interact with the
VEF runtime. Every function registered via `make_func<>` must follow them.
The types referenced below are available via `#include <villagesql/vsql.h>`.

### Part A: VDF Function Contracts

**1. VDF implementation functions are `void` -- they never return a value.**

```cpp theme={null}
void my_func_impl(StringArg input, StringResult out) {
    // ... compute result ...
    return;  // always void -- no return value
}
```

Communicate success, NULL, warning, or error by calling one terminal method on
the result wrapper: `out.set(...)` / `out.set_length(n)`, `out.set_null()`,
`out.warning(msg)`, or `out.error(msg)`.

**2. Set `result->type` to exactly one of the four result constants.**

Four constants exist in `vef_return_value_type_t`:

| Constant             | Value | Meaning                                                                                                                                                                |
| -------------------- | ----- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `VEF_RESULT_VALUE`   | 0     | Success -- the output is in the appropriate union field                                                                                                                |
| `VEF_RESULT_NULL`    | 1     | The result is SQL NULL                                                                                                                                                 |
| `VEF_RESULT_WARNING` | 2     | Row-level warning -- execution continues, NULL is returned for this row, and a SQL warning is added. In strict mode, MySQL promotes this to an error on INSERT/UPDATE. |
| `VEF_RESULT_ERROR`   | 3     | Fatal error -- statement execution is aborted; message is in `result->error_msg`                                                                                       |

There are no type-specific variants. `VEF_RESULT_VALUE` is the single success
constant for strings, integers, reals, and custom types alike. The output type
is determined by which result wrapper your function takes (`StringResult`,
`IntResult`, `RealResult`, `CustomResult`).

**3. Check `input.is_null()` before calling `input.value()`.**

If `is_null()` returns true, calling `value()` is undefined behavior.

```cpp theme={null}
void my_func_impl(StringArg input, StringResult out) {
    if (input.is_null()) {
        out.set_null();
        return;
    }
    // Safe to call input.value() -> std::string_view
}
```

**4. For string results, write into `out.buffer()` and call `out.set_length(n)`. Check `out.buffer().size()` before writing.**

* `out.buffer()` returns a `Span<char>` over the server-managed buffer.
* `out.set_length(n)` records how many bytes were written.
* `out.buffer().size()` is the maximum capacity. Always check it before writing.

```cpp theme={null}
void upper_impl(StringArg input, StringResult out) {
    if (input.is_null()) {
        out.set_null();
        return;
    }

    auto sv = input.value();
    auto buf = out.buffer();
    if (sv.size() > buf.size()) {
        out.error("Input length exceeds buffer size");
        return;
    }

    for (size_t i = 0; i < sv.size(); i++) {
        buf.data()[i] = toupper(sv[i]);
    }
    out.set_length(sv.size());
}
```

**5. Pass error messages to `out.error(msg)`. The message is truncated to `VEF_MAX_ERROR_LEN` (512 bytes) if necessary.**

`out.error(msg)` accepts a `std::string_view`. It copies the message into a
server-managed buffer and sets the result state to error in one call.

```cpp theme={null}
// Correct — error goes through out.error()
out.error("Invalid input: expected positive integer");
return;
```

## Implement Wrapper Functions

Implementation functions use typed argument and result wrappers:

```cpp theme={null}
#include <villagesql/vsql.h>
#include <algorithm>

using namespace vsql;

// String reverse implementation
void my_reverse_impl(StringArg input, StringResult out) {
    if (input.is_null()) { out.set_null(); return; }

    auto sv = input.value();
    auto buf = out.buffer();
    for (size_t i = 0; i < sv.size(); i++) {
        buf.data()[i] = sv[sv.size() - 1 - i];
    }
    out.set_length(sv.size());
}

// Count vowels implementation
void count_vowels_impl(StringArg input, IntResult out) {
    if (input.is_null()) { out.set_null(); return; }

    long long count = 0;
    for (char c : input.value()) {
        char lower = std::tolower(c);
        if (lower == 'a' || lower == 'e' || lower == 'i' ||
            lower == 'o' || lower == 'u') {
            count++;
        }
    }
    out.set(count);
}
```

## Handling NULL Values

Check for NULL via `is_null()` and return NULL by calling `set_null()`:

```cpp theme={null}
void my_func_impl(StringArg input, StringResult out) {
    if (input.is_null()) {
        out.set_null();
        return;
    }

    auto sv = input.value();
    auto buf = out.buffer();
    // ... write into buf.data(), up to buf.size() bytes ...

    out.set_length(output_length);
}
```

**NULL handling options:**

* **Input NULL check:** `input.is_null()`
* **Return NULL:** `out.set_null()`
* **Return value:** `out.set(v)` (numeric/custom) or `out.set_length(n)` after writing into `out.buffer()` (string)
* **Return warning:** `out.warning(msg)` — returns NULL for this row, adds a SQL warning, continues execution; in strict mode, MySQL promotes this to an error on INSERT/UPDATE. Call instead of `out.set()`, not in addition to it.
* **Return error:** `out.error(msg)` — aborts statement execution

## Error Handling

Return errors with custom messages for validation failures or invalid input:

```cpp theme={null}
void validate_age_impl(IntArg age_input, IntResult out) {
    if (age_input.is_null()) {
        out.set_null();
        return;
    }

    long long age = age_input.value();

    if (age < 0 || age > 150) {
        out.error("Age must be between 0 and 150");
        return;
    }

    out.set(age);
}
```

**Result types:**

* `VEF_RESULT_VALUE` - Success (`out.set(v)` / `out.set_length(n)`)
* `VEF_RESULT_NULL` - NULL value (`out.set_null()`)
* `VEF_RESULT_WARNING` - Row-level warning (returns NULL, adds SQL warning, continues execution; strict mode promotes to error on INSERT/UPDATE) (`out.warning(msg)`)
* `VEF_RESULT_ERROR` - Fatal error, aborts statement execution (`out.error(msg)`)

## Per-Statement State with Prerun/Postrun

Prerun and postrun hooks use typed wrappers. The required signatures are:

```cpp theme={null}
void my_prerun(vsql::PrerunArgs args, vsql::PrerunResult out);
void my_postrun(vsql::PostrunArgs args);
```

Raw ABI signatures (`vef_prerun_args_t*` / `vef_postrun_args_t*`) are rejected
at compile time by `static_assert` in `.prerun<&Hook>()` and `.postrun<&Hook>()`.

```cpp theme={null}
#include <villagesql/vsql.h>
using namespace vsql;

struct CallCounter { long long n = 0; };

void my_prerun(PrerunArgs, PrerunResult out) {
    out.set_user_data(new CallCounter{});
}

void my_func_impl(CallCounter& state, IntResult out) {
    state.n++;
    out.set(state.n);
}

void my_postrun(PostrunArgs args) {
    args.delete_state<CallCounter>();
}

VEF_GENERATE_ENTRY_POINTS(
  make_extension()
    .func(make_func<&my_func_impl>("my_func")
      .returns(INT).no_params()
      .prerun<&my_prerun>()
      .postrun<&my_postrun>()
      .build())
);
```

For `PrerunArgs` and `PostrunArgs` method details, see
[Per-Statement State](/docs/mysql-8.4/0.0.4/development#per-statement-state-prerun-and-postrun)
in the Development guide.

<Note>
  **Most extensions don't need prerun/postrun hooks.** The VEF SDK automatically
  handles common cases like type checking and result buffer sizing — for both
  STRING-returning and CUSTOM-returning VDFs, the result buffer is grown to fit
  the resolved return type before the VDF body runs. Use prerun/postrun only when
  you need expensive per-statement setup (like opening connections) that
  shouldn't happen per-row.

  If you find you need prerun/postrun for your use case, share your scenario on
  the [VillageSQL Discord](https://discord.gg/KSr6whd3Fr) — the team may be able
  to add SDK support to handle it automatically.
</Note>

## Aggregate Functions

Built-in aggregates COUNT(DISTINCT), MIN, MAX, and GROUP\_CONCAT work with
custom types out of the box. MIN and MAX require a compare function registered
on the type.

Custom aggregate VDFs are also supported. Register one with
`make_aggregate_func<State, &result_fn>("name")`, then chain `.returns()`,
`.param()`, `.clear<>()`, and `.accumulate<>()` before calling `.build()`.
Both `.clear<>()` and `.accumulate<>()` are required. See
[Aggregate VDFs](/docs/mysql-8.4/0.0.4/development#aggregate-vdfs) for the
builder API and callback signatures.

**Built-in aggregate operations with custom types:**

```sql theme={null}
-- COUNT(DISTINCT) works with custom types
SELECT COUNT(DISTINCT impedance) FROM signals;

-- MIN and MAX work with custom types (requires compare function)
SELECT MIN(impedance), MAX(impedance) FROM signals;

-- GROUP_CONCAT works with custom types
SELECT GROUP_CONCAT(impedance ORDER BY impedance SEPARATOR ', ') FROM signals;
```

Extension functions are called in a per-row execution model:

* Each function call processes one row with its own result buffer (thread-safe)
* `prerun`/`postrun` provide per-statement setup/teardown
* **Avoid global state** - use function parameters and return values instead
* If you must use global state, protect it with mutexes/locks

**Best practice:** Design functions to be stateless for simplicity and safety.

## Window Functions

The following window functions work with custom types:

```sql theme={null}
SELECT
    id,
    impedance,
    LAG(impedance)  OVER (ORDER BY id) AS prev_impedance,
    LEAD(impedance) OVER (ORDER BY id) AS next_impedance
FROM signals;

SELECT
    id,
    impedance,
    FIRST_VALUE(impedance) OVER w AS first_impedance,
    LAST_VALUE(impedance)  OVER w AS last_impedance,
    NTH_VALUE(impedance, 2) OVER w AS second_impedance
FROM signals
WINDOW w AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
```

## Temporary Tables

Custom types work in temporary tables. `CREATE TEMPORARY TABLE`, `INSERT`,
and `ALTER TABLE` behave the same as with permanent tables.

```sql theme={null}
CREATE TEMPORARY TABLE tmp_signals (
    id        INT PRIMARY KEY,
    impedance COMPLEX
);

INSERT INTO tmp_signals VALUES (1, '(10,5)'), (2, '(20,0)');
SELECT id, impedance FROM tmp_signals;
```

## Preview APIs

Some VEF capabilities are available as opt-in headers under
`villagesql/preview/` in the SDK include tree. The ABI and API are still
under active development and may change without notice.

To opt in, add the include to your extension source. For example:

```cpp theme={null}
#include <villagesql/preview/keyring.h>        // vsql::preview_keyring::KeyringCapability
#include <villagesql/preview/thread_worker.h>  // vsql::preview_thread_worker::ThreadWorkerCapability
#include <villagesql/preview/sql_query.h>      // vsql::preview_sql_query::SqlQueryCapability
```

None of these headers are pulled in by `<villagesql/vsql.h>`; you must include it
directly when you opt in.

Namespace layout under `vsql::preview` is per-capability — there is no single
universal pattern. The keyring API uses `vsql::preview_keyring::KeyringCapability`;
the thread worker API uses `vsql::preview_thread_worker::ThreadWorkerCapability`;
the SQL query API uses `vsql::preview_sql_query::SqlQueryCapability` and must be
opened from a background worker thread handle (`vef_thread_handle_t *`).
Check each header for the exact namespace and class name it defines.

For the full preview API documentation, see [Preview Capabilities](/docs/mysql-8.4/0.0.4/preview-capabilities).

<Warning>
  Preview headers are not stable. An extension built against them may break
  when the server is updated. When a feature stabilizes, its headers move to a
  versioned stable SDK path.
</Warning>

## Triggers

Triggers fire on tables with custom type columns. The trigger body can
reference non-custom-type columns from `NEW` and `OLD`. Accessing custom
type column values inside a trigger body is not yet supported.

```sql theme={null}
CREATE TABLE signals (
    id        INT PRIMARY KEY,
    impedance COMPLEX,
    label     VARCHAR(50)
);
CREATE TABLE signal_log (
    id        INT,
    label     VARCHAR(50),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER signals_after_insert
AFTER INSERT ON signals
FOR EACH ROW
    INSERT INTO signal_log (id, label) VALUES (NEW.id, NEW.label);

INSERT INTO signals VALUES (1, '(10,5)', 'sensor_a');
SELECT id, label FROM signal_log;
```