// ... read src, write result to dst ...
out.set_length(src.size());
}
```
Register with `.param(COMPLEX)` and `.returns(COMPLEX)`:
```cpp theme={null}
.func(make_func<&complex_conjugate_impl>("complex_conjugate")
.returns(COMPLEX)
.param(COMPLEX)
.build())
```
See the [development guide](/docs/mysql-8.4/0.0.3/development#typed-wrappers-recommended)
for the full `CustomArg`/`CustomResult` API, including `CustomArgWith`
and `CustomResultWith
` for parameterized types.
### Deterministic Functions
By default, VDFs are registered as non-deterministic. A non-deterministic function is blocked from three SQL contexts: generated columns, CHECK constraints, and expression default values (`DEFAULT (expr)` on a column) — using any of these features with a non-deterministic VDF returns an error. If your function always produces the same output for the same inputs and has no side effects, you can declare it deterministic by adding `.deterministic()` to the builder chain.
The optimizer may use this information to evaluate the function once per statement and reuse the value across rows, rather than calling it per row. Incorrectly marking a non-deterministic function as deterministic can therefore cause the server to return the same result for inputs that should produce different outputs. Only add `.deterministic()` when your function truly has no dependency on external state, randomness, or time.
**Builder signature:** `.deterministic(bool d = true)` — the zero-argument form defaults to `true`.
**Example:**
```cpp theme={null}
.func(make_func<&complex_add_impl>("complex_add")
.returns(COMPLEX)
.param(COMPLEX)
.param(COMPLEX)
.deterministic()
.build())
```
Because `complex_add` is declared deterministic, it can be used in a generated column definition:
```sql theme={null}
-- Deterministic VDFs can be used in generated columns
CREATE TABLE t (
a COMPLEX,
b COMPLEX,
result COMPLEX GENERATED ALWAYS AS (complex_add(a, b)) STORED
);
-- STORED vs VIRTUAL follows standard MySQL generated column rules
```
### Custom Buffer Sizes
For functions returning variable-length data, request a specific buffer size:
```cpp theme={null}
make_func<&large_result_impl>("large_result")
.returns(STRING)
.param(INT)
.buffer_size(65536) // Request 64KB buffer
.build()
```
Check available buffer space before writing:
```cpp theme={null}
void large_result_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
size_t needed = calculate_output_size(input);
if (needed > result->max_str_len) {
result->type = VEF_RESULT_ERROR;
strcpy(result->error_msg, "Output exceeds buffer size");
return;
}
// Write output to result->str_buf
result->type = VEF_RESULT_VALUE;
result->actual_len = actual_output_length;
}
```
Request sufficient buffer size via `.buffer_size()` based on your function's maximum output size.
## Critical API 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 defined in `#include `.
### Part A: VDF Function Contracts
**1. VDF implementation functions are `void` -- they never return a value.**
The server calls your function through this typedef:
```cpp theme={null}
typedef void (*vef_vdf_func_t)(vef_context_t *ctx, vef_vdf_args_t *args,
vef_vdf_result_t *result);
```
Extension authors write the per-argument form that the SDK wrapper bridges to
this ABI:
```cpp theme={null}
void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
// ... compute result ...
return; // always void -- no return value
}
```
Communicate success, NULL, or error exclusively through `result->type`.
**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 type of
output is determined by which union field you write to (`str_buf`,
`int_value`, `real_value`, `bin_buf`).
**3. Check `input->is_null` before reading any other field on an input argument.**
If `is_null` is true, every other field on that `vef_invalue_t` is undefined.
Accessing `str_value`, `int_value`, or any other field without checking
`is_null` first is undefined behavior.
```cpp theme={null}
void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
if (input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
// Safe to read input->str_value, input->int_value, etc.
}
```
**4. For string results, write to `result->str_buf` and set `result->actual_len`. Check `result->max_str_len` before writing.**
* `result->str_buf` is a server-managed buffer. Write your output here.
* `result->actual_len` must be set to the number of bytes written. There is no field named `str_len` on the result struct.
* `result->max_str_len` is the size of `str_buf` in bytes. Always check it before writing. Do not use `sizeof(result->str_buf)`.
```cpp theme={null}
void upper_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
if (input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
size_t len = input->str_len;
if (len > result->max_str_len) {
result->type = VEF_RESULT_ERROR;
snprintf(result->error_msg, VEF_MAX_ERROR_LEN,
"Input length %zu exceeds buffer size %zu",
len, result->max_str_len);
return;
}
for (size_t i = 0; i < len; i++) {
result->str_buf[i] = toupper(input->str_value[i]);
}
result->type = VEF_RESULT_VALUE;
result->actual_len = len;
}
```
**5. Write error messages to `result->error_msg`, not to `result->str_buf`. Use `VEF_MAX_ERROR_LEN` (512 bytes) as the `snprintf` size limit.**
`error_msg` is a separate, caller-provided buffer dedicated to error text.
It is independent of `str_buf`. The maximum size including the null
terminator is `VEF_MAX_ERROR_LEN` (512).
```cpp theme={null}
// Correct -- error goes to error_msg
result->type = VEF_RESULT_ERROR;
snprintf(result->error_msg, VEF_MAX_ERROR_LEN,
"Invalid input: expected positive integer, got %lld",
input->int_value);
return;
```
### Part B: Encode and Decode Return Convention
The bool return convention applies **only** to encode and decode **function pointers** used by custom types. It does not apply to VDF-name type operations (which use the standard VDF `void` convention) or to VDF implementation functions.
```cpp theme={null}
typedef bool (*vef_encode_func_t)(unsigned char *buffer, size_t buffer_size,
const char *from, size_t from_len,
size_t *length);
typedef bool (*vef_decode_func_t)(const unsigned char *buffer,
size_t buffer_size, char *to, size_t to_size,
size_t *to_length);
```
Both functions return `bool` where:
* **`false` = success** (the operation completed without error)
* **`true` = error** (the conversion failed)
The encode/decode return convention is inverted from what most developers
expect. `false` means success, `true` means error. This follows the general
convention of MySQL internal C/C++ APIs, which frequently return `0` (`false`)
for success. Double-check every `return` statement in your encode and decode
implementations.
***
### Implement Wrapper Functions
Implementation functions use the VEF API signature:
```cpp theme={null}
#include
#include
#include
// String reverse implementation
void my_reverse_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
if (input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
// Reverse the string
size_t len = input->str_len;
for (size_t i = 0; i < len; i++) {
result->str_buf[i] = input->str_value[len - 1 - i];
}
result->type = VEF_RESULT_VALUE;
result->actual_len = len;
}
// Count vowels implementation
void count_vowels_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
if (input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
int64_t count = 0;
for (size_t i = 0; i < input->str_len; i++) {
char lower = std::tolower(input->str_value[i]);
if (lower == 'a' || lower == 'e' || lower == 'i' ||
lower == 'o' || lower == 'u') {
count++;
}
}
result->int_value = count;
result->type = VEF_RESULT_VALUE;
}
```
### Handling NULL Values
Check for NULL via the `is_null` flag and return NULL by setting the result type:
```cpp theme={null}
void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
vef_vdf_result_t* result) {
// Check if input is NULL
if (input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
// Access input data
const char* str = input->str_value;
size_t len = input->str_len;
// Process and write result to result->str_buf
// ...
result->type = VEF_RESULT_VALUE;
result->actual_len = output_length;
}
```
**NULL handling options:**
* **Input NULL check:** `if (input->is_null)`
* **Return NULL:** `result->type = VEF_RESULT_NULL`
* **Return value:** `result->type = VEF_RESULT_VALUE` + write to type-specific buffer
* **Return warning:** `result->type = VEF_RESULT_WARNING` + warning message in `result->error_msg` (returns NULL for this row, adds SQL warning, continues execution; in strict mode, MySQL promotes this to an error on INSERT/UPDATE)
* **Return error:** `result->type = VEF_RESULT_ERROR` + error message in `result->error_msg`
### Error Handling
Return errors with custom messages for validation failures or invalid input:
```cpp theme={null}
void validate_age_impl(vef_context_t* ctx, vef_invalue_t* age_input,
vef_vdf_result_t* result) {
if (age_input->is_null) {
result->type = VEF_RESULT_NULL;
return;
}
long long age = age_input->int_value;
// Validate input range
if (age < 0 || age > 150) {
result->type = VEF_RESULT_ERROR;
// Write error message
const char* msg = "Age must be between 0 and 150";
strcpy(result->error_msg, msg);
return;
}
// Return valid result
result->type = VEF_RESULT_VALUE;
result->int_value = age;
}
```
**Result types:**
* `VEF_RESULT_VALUE` - Success
* `VEF_RESULT_NULL` - NULL value
* `VEF_RESULT_WARNING` - Row-level warning (returns NULL, adds SQL warning, continues execution; strict mode promotes to error on INSERT/UPDATE)
* `VEF_RESULT_ERROR` - Fatal error (aborts statement execution)
***
## Step 4: Creating Custom Types
Custom types allow you to define new column types that work seamlessly with SQL operations like ORDER BY, GROUP BY, and indexes.
### Define Type Operations
Every custom type needs encode, decode, and compare operations, and optionally a hash operation. There are two ways to provide these: **VDF names** (VEF\_PROTOCOL\_2 or later, recommended) or **function pointers**. For each operation, provide one or the other — not both.
**VDF names** — reference VDFs from the same extension by name (see [VDF-Name Type Operations](#vdf-name-type-operations)):
```cpp theme={null}
// Encode: string -> binary. false = success, true = error.
// Set *length = SIZE_MAX to produce SQL NULL.
bool mytype_from_string(std::string_view from,
villagesql::Span buf,
size_t *length) { /* ... */ }
// Decode: binary -> string. false = success, true = error.
bool mytype_to_string(villagesql::Span data,
villagesql::Span out,
size_t *out_len) { /* ... */ }
// Compare: returns <0, 0, or >0.
int mytype_compare(villagesql::Span a,
villagesql::Span b) { /* ... */ }
// Hash: returns hash code (optional).
size_t mytype_hash(villagesql::Span data) { /* ... */ }
```
**Function pointers** (`false` = success, `true` = error — see [Part B](#part-b-encode-and-decode-return-convention)):
```cpp theme={null}
// Encode: Convert string representation to binary
bool encode_mytype(unsigned char* buffer, size_t buffer_size,
const char* from, size_t from_len, size_t* length) {
// Parse 'from' string and write binary to 'buffer'
// Write binary to 'buffer' and set *length to bytes written
// Return false on success, true on error (e.g., set *length = 0)
}
// Decode: Convert binary to string representation
bool decode_mytype(const unsigned char* buffer, size_t buffer_size,
char* to, size_t to_size, size_t* to_length) {
// Read binary from 'buffer' and write string to 'to'
// Set *to_length to string length
// Return false on success, true on error
}
// Compare: enables ORDER BY and indexing (required)
int compare_mytype(const unsigned char* data1, size_t len1,
const unsigned char* data2, size_t len2) {
// Return: negative if data1data2
}
// Hash: custom hash (optional, uses default binary hash if omitted)
size_t hash_mytype(const unsigned char* data, size_t len) {
// Return hash value for the binary data
}
```
### Register the Type
**Using VDF names** (VEF\_PROTOCOL\_2 required; see [VDF-Name Type Operations](#vdf-name-type-operations)):
```cpp theme={null}
VEF_GENERATE_ENTRY_POINTS(
make_extension("my_extension", "1.0.0")
.type(make_type(MYTYPE)
.persisted_length(16)
.max_decode_buffer_length(64)
.encode("mytype_from_string") // VDF name instead of &func
.decode("mytype_to_string")
.compare("mytype_compare") // Enables ORDER BY and indexes
.hash("mytype_hash") // Optional
.build())
.func(make_type_encode<&mytype_from_string>("mytype_from_string", MYTYPE))
.func(make_type_decode<&mytype_to_string>("mytype_to_string", MYTYPE))
.func(make_type_compare<&mytype_compare>("mytype_compare", MYTYPE))
.func(make_type_hash<&mytype_hash>("mytype_hash", MYTYPE))
);
```
The named VDFs must also be registered with `.func()` in the same extension.
**Using function pointers:**
```cpp theme={null}
VEF_GENERATE_ENTRY_POINTS(
make_extension("my_extension", "1.0.0")
.type(make_type(MYTYPE)
.persisted_length(16) // Fixed storage size in bytes
.max_decode_buffer_length(64) // Max string representation size
.encode(&encode_mytype)
.decode(&decode_mytype)
.compare(&compare_mytype) // Enables ORDER BY and indexes
.hash(&hash_mytype) // Optional custom hash
.build())
.func(make_func<&mytype_constructor>("MYTYPE") // Constructor function
.returns(MYTYPE)
.param(REAL)
.param(REAL)
.build())
);
```
### VDF-Name Type Operations
Each VDF named via `.encode()`, `.decode()`, `.compare()`, or `.hash()` must be registered with the matching `make_type_*` builder in the same extension:
| Operation | Builder | VDF SQL Signature |
| --------- | ------------------------------------- | ----------------------------------------------- |
| `encode` | `make_type_encode<&f>("name", TYPE)` | `(STRING) -> CUSTOM(this type)` |
| `decode` | `make_type_decode<&f>("name", TYPE)` | `(CUSTOM(this type)) -> STRING` |
| `compare` | `make_type_compare<&f>("name", TYPE)` | `(CUSTOM(this type), CUSTOM(this type)) -> INT` |
| `hash` | `make_type_hash<&f>("name", TYPE)` | `(CUSTOM(this type)) -> INT` |
VDF-name operations use the `bool` return convention (`false` = success, `true` = error), same as function-pointer encode/decode. See [development.mdx — Type Operation Builders](/docs/mysql-8.4/0.0.3/development) for the full C++ signatures.
### ALTER TABLE and Custom Types
`ALTER TABLE ... MODIFY COLUMN` and `CHANGE COLUMN` enforce these rules when custom types are involved:
| From | To | Result |
| ---------- | --------------------- | ------------------------------------------------------------------------------------- |
| Non-custom | Custom | Error: `Cannot convert column 'col' to custom type 'MYTYPE'` |
| Custom | String type | Allowed |
| Custom | Non-string type | Error: `Cannot convert custom type column 'col' to non-string type` |
| Custom | Different custom type | Error if incompatible: `Cannot convert between incompatible custom types 'A' and 'B'` |
***
### Type Conversion Functions
Expose your encode/decode functions as SQL functions for explicit type conversion:
```cpp theme={null}
VEF_GENERATE_ENTRY_POINTS(
make_extension("my_extension", "1.0.0")
.type(make_type(MYTYPE)
.persisted_length(16)
.max_decode_buffer_length(64)
.encode(&encode_mytype)
.decode(&decode_mytype)
.compare(&compare_mytype)
.build())
// String → Custom Type conversion
.func(make_func("mytype_from_string")
.from_string<&encode_mytype>(MYTYPE))
// Custom Type → String conversion
.func(make_func("mytype_to_string")
.to_string<&decode_mytype>(MYTYPE))
.build()
);
```
**Usage in SQL:**
```sql theme={null}
-- Convert string to custom type
SELECT mytype_from_string('(1.0,2.0)');
-- Convert custom type to string
SELECT mytype_to_string(my_column) FROM my_table;
-- Explicit conversion in INSERT
INSERT INTO my_table (id, value)
VALUES (1, mytype_from_string('(3.0,4.0)'));
```
The `.from_string<>()` and `.to_string<>()` builders automatically create SQL-callable conversion functions using your encode/decode implementations.
**When explicit conversion is required.** VillageSQL implicitly converts a string
literal to a custom type on direct column assignment, so
`INSERT INTO t (val) VALUES ('(1.0,2.0)')` works without an explicit call. But
expressions that resolve to `STRING` type — `CASE` expressions, `CONCAT`, and
similar — are not implicitly coerced. Wrap them with the `from_string` VDF:
```sql theme={null}
UPDATE my_table
SET val = mytype_from_string(
CASE (pk MOD 2)
WHEN 0 THEN '(1.0,2.0)'
ELSE '(0.0,0.0)'
END
);
```
### Example: COMPLEX Type
Here's a complete example implementing a COMPLEX number type:
```cpp theme={null}
#include
#include
// Encode complex number from "(real,imag)" to 16 bytes
bool encode_complex(std::string_view from,
villagesql::Span buf,
size_t *length) {
if (buf.size() < 16) return true;
double real, imag;
if (sscanf(from.data(), "(%lf,%lf)", &real, &imag) != 2) return true;
memcpy(buf.data(), &real, 8);
memcpy(buf.data() + 8, &imag, 8);
*length = 16;
return false;
}
// Decode 16 bytes to "(real,imag)" string
bool decode_complex(villagesql::Span data,
villagesql::Span out,
size_t *out_len) {
if (data.size() < 16) return true;
double real, imag;
memcpy(&real, data.data(), 8);
memcpy(&imag, data.data() + 8, 8);
int len = snprintf(out.data(), out.size(), "(%.6f,%.6f)", real, imag);
if (len < 0 || len >= (int)out.size()) return true;
*out_len = len;
return false;
}
// Compare for ORDER BY: compare real part first, then imaginary
int compare_complex(villagesql::Span a,
villagesql::Span b) {
if (a.size() < 16 || b.size() < 16) return 0;
double a_real, a_imag, b_real, b_imag;
memcpy(&a_real, a.data(), 8);
memcpy(&a_imag, a.data() + 8, 8);
memcpy(&b_real, b.data(), 8);
memcpy(&b_imag, b.data() + 8, 8);
if (a_real < b_real) return -1;
if (a_real > b_real) return 1;
if (a_imag < b_imag) return -1;
if (a_imag > b_imag) return 1;
return 0;
}
```
After defining these operations, users can create tables with your custom type:
```sql theme={null}
CREATE TABLE signals (
id INT PRIMARY KEY,
impedance COMPLEX,
frequency_response COMPLEX
);
INSERT INTO signals VALUES (1, '(50.0,10.0)', '(0.95,0.31)');
-- ORDER BY works because we provided compare_complex!
SELECT * FROM signals ORDER BY impedance;
-- Prepared statements work with custom types
PREPARE stmt FROM 'SELECT * FROM signals WHERE impedance = ?';
SET @val = '(50.0,10.0)';
EXECUTE stmt USING @val;
-- Aggregate operations work with custom types
SELECT COUNT(DISTINCT impedance), MIN(impedance), MAX(impedance),
GROUP_CONCAT(impedance ORDER BY impedance) FROM signals;
```
### VDFs in Generated Columns
VDFs can be used in generated column expressions. The VDF must be declared `.deterministic()` in the extension builder — the server blocks non-deterministic functions in this context.
```sql theme={null}
CREATE TABLE signals (
id INT PRIMARY KEY,
impedance COMPLEX,
-- Generated column computed by a VDF
magnitude DOUBLE GENERATED ALWAYS AS (complex_abs(impedance)) STORED
);
```
`complex_abs` must be registered with `.deterministic()`. Traditional MySQL UDFs are not permitted in generated columns.
See [vsql\_complex Example](/docs/mysql-8.4/0.0.3/examples) for the complete implementation.
### VDFs in Functional Indexes
VDFs can be used in functional index expressions. The same `.deterministic()`
requirement from generated columns applies here because MySQL implements
functional indexes as hidden generated columns.
```sql theme={null}
CREATE TABLE signals (
id INT PRIMARY KEY,
sig COMPLEX,
INDEX idx_magnitude ((COMPLEX_ABS(sig)))
);
```
The optimizer uses the index when the same VDF expression appears in `WHERE`,
`ORDER BY`, or `GROUP BY`. Cast the comparison value to the VDF's return type
so the optimizer matches the expression:
```sql theme={null}
SELECT id FROM signals WHERE COMPLEX_ABS(sig) > CAST(20.0 AS DOUBLE);
```
***
## Step 5: Update Build Configuration
Edit `CMakeLists.txt` to build your extension as a VEB file:
```cmake theme={null}
cmake_minimum_required(VERSION 3.16)
project(my_extension)
# Find VillageSQL Extension Framework
find_package(VillageSQLExtensionFramework QUIET
PATHS "${CMAKE_CURRENT_SOURCE_DIR}/../../../cmake"
)
# The framework detects build flags via 4 methods (in order):
# 1. Explicit MYSQL_INCLUDE_FLAGS/MYSQL_CXXFLAGS
# 2. VillageSQL_BUILD_DIR (reads CMakeCache.txt from VillageSQL build)
# 3. VSQL_BASE_DIR (uses mysql_config)
# 4. Default - mysql_config from PATH
# Build shared library with your source files
add_library(extension SHARED
src/extension.cc
src/my_functions.cc
)
# Create VEB archive
VEF_CREATE_VEB(
NAME my_extension
LIBRARY_TARGET extension
MANIFEST ${CMAKE_CURRENT_SOURCE_DIR}/manifest.json
)
# Install VEB to VillageSQL extensions directory
install(FILES ${VEB_OUTPUT} DESTINATION ${INSTALL_DIR})
```
**Configuration notes:**
* `VillageSQLExtensionFramework` provides CMake helpers for building extensions
* `VEF_CREATE_VEB()` packages your library, manifest, and metadata into a `.veb` archive
* The framework automatically detects MySQL/VillageSQL build flags
* Library target name is typically `extension` (can be anything)
* VEB name must match your manifest.json name
* By default, extensions build against the stable ABI headers. Set `-DVSQL_USE_DEV_ABI=ON` to build against the unstable dev headers instead
***
## Step 6: Create a Build Directory
Create a separate build directory:
```bash theme={null}
mkdir build
cd build
```
## Step 7: Build with CMake and Make
Configure and build your extension:
```bash theme={null}
# Configure the build
cmake ..
# Or, if building against VillageSQL source:
cmake .. -DVillageSQL_BUILD_DIR=/path/to/villagesql/build
# Or, to build against the unstable dev ABI headers:
cmake .. -DVSQL_USE_DEV_ABI=ON
# Build the extension
make
```
This creates:
* Compiled shared library (`.so` file)
* VEB package (`.veb` file) - a tar archive containing manifest and library
### Verify the Build
Check the contents of your VEB file:
```bash theme={null}
make show_veb
```
You should see:
```
manifest.json
lib/myext.so
```
## Step 8: Install and Test
### Option A: Install to VillageSQL Extensions Directory
Use the install target to copy the VEB to your VillageSQL installation:
```bash theme={null}
make install
```
This copies the `.veb` file to the directory configured via `VillageSQL_VEB_INSTALL_DIR`.
### Option B: Manual Installation
Copy the VEB file manually:
```bash theme={null}
# Find the VEF directory
mysql -u root -p -e "SHOW VARIABLES LIKE 'veb_dir';"
# Copy the VEB file
sudo cp my-awesome-extension.veb /path/to/veb_dir/
```
### Test Your Extension
1. **Connect to VillageSQL**:
```bash theme={null}
mysql -u root -p
```
2. **Install the extension**:
```sql theme={null}
INSTALL EXTENSION my_awesome_extension;
```
3. **Verify installation**:
```sql theme={null}
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;
```
4. **Test your functions**:
```sql theme={null}
SELECT my_reverse('Hello, World!');
-- Output: !dlroW ,olleH
SELECT count_vowels('VillageSQL');
-- Output: 3
```
## Creating Tests
Add test files to validate your extension works correctly:
1. **Create a test file** in `test/t/`:
```sql theme={null}
-- test/t/my_basic.test
SELECT my_reverse('abc');
SELECT my_reverse('');
SELECT my_reverse(NULL);
```
2. **Generate expected results**:
```bash theme={null}
cd /path/to/villagesql/build/mysql-test
perl mysql-test-run.pl --suite=/path/to/your/extension/test --record
```
3. **Run tests**:
```bash theme={null}
perl mysql-test-run.pl --suite=/path/to/your/extension/test
```
## Advanced Topics
### Per-Statement State with Prerun/Postrun
For VEF SDK functions that need setup/cleanup per SQL statement (not per row):
```cpp theme={null}
// Called once before the first row
void my_prerun(vef_context_t* ctx, vef_prerun_args_t* args,
vef_prerun_result_t* result) {
// Allocate state, open connections, etc.
void* state = malloc(sizeof(MyState));
result->user_data = state;
}
// Called once after all rows processed
void my_postrun(vef_context_t* ctx, vef_postrun_args_t* args,
vef_postrun_result_t* result) {
// Clean up state allocated in prerun
if (args->user_data) {
free(args->user_data);
}
}
// Register with extension
VEF_GENERATE_ENTRY_POINTS(
make_extension("my_ext", "1.0.0")
.func(make_func<&my_func_impl>("my_func")
.returns(STRING)
.param(STRING)
.prerun(&my_prerun)
.postrun(&my_postrun)
.build())
);
```
**Most extensions don't need prerun/postrun hooks.** The VEF SDK automatically handles common cases like type checking and buffer allocation. 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, please share your scenario on the [VillageSQL Discord](https://discord.gg/KSr6whd3Fr) - the team may be able to add SDK support to handle it automatically.
### Aggregate Functions
Only COUNT(DISTINCT), MIN, MAX, and GROUP\_CONCAT are supported for custom types. SUM, AVG, and other aggregate functions are not.
**Supported 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;
```
### 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;
```
## Troubleshooting
### Extension Won't Load
Check the error log and verify the VEB contents:
```bash theme={null}
make show_veb
tail -f /var/log/mysql/error.log
```
### Function Not Found
Verify installation and registration:
```sql theme={null}
SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;
```
### Build Errors
```bash theme={null}
# Verify mysql_config is available
which mysql_config
mysql_config --version
# Check compiler version
gcc --version # or clang --version
# Verify CMake version (3.16+ required)
cmake --version
```
## Example Extensions
Learn from existing VillageSQL extensions:
Complex number data type implementation
Minimal template for creating extensions
## Next Steps
Learn how to install and manage extensions
Build VillageSQL from source code
Contribute to VillageSQL Server
MySQL UDF API reference documentation
## Resources
* [VillageSQL Extension Template](https://github.com/villagesql/vsql-extension-template)
* [MySQL UDF API Documentation](https://dev.mysql.com/doc/extending-mysql/8.4/en/adding-loadable-function.html)
* [CMake Documentation](https://cmake.org/documentation/)
* [VillageSQL Community Discord](https://discord.gg/KSr6whd3Fr)