// ... 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](/mysql-8.4/0.0.5/development#argument-and-result-types)
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()
```
When you build the value yourself with `buffer()` and `set_length()`, the buffer
is fixed at `buffer().size()` bytes — writing past it overflows memory, so guard
against it:
```cpp theme={null}
void large_result_impl(StringArg input, StringResult out) {
if (input.is_null()) { out.set_null(); return; }
size_t needed = calculate_output_size(input.value());
auto buf = out.buffer();
if (needed > buf.size()) {
out.error("Output exceeds buffer size");
return;
}
// Write output into buf.data()
out.set_length(actual_output_length);
}
```
For STRING results, `out.set(sv)` follows a snprintf-style overflow contract: it
copies as many bytes as fit and reports the value's full size via `set_length`.
When the reported size exceeds the buffer, the server grows the result buffer and
re-invokes the function, so a STRING value larger than the requested buffer is no
longer truncated.
That contract applies at row time. A *materialized* STRING result — in a
GROUP BY/DISTINCT temp table, `CREATE TABLE ... SELECT`, or UNION — still
truncates at the argument width unless the function declares
`.max_result_length(n)`, which sizes the result column (in characters, capped at
`VEF_MAX_RESULT_LENGTH`, 16 MiB):
```cpp theme={null}
make_func<&large_result_impl>("large_result")
.returns(STRING)
.max_result_length(65536)
.build()
```
Request sufficient buffer size via `.buffer_size()` based on your function's maximum output size. Sizing the buffer correctly avoids the cost of a grow-and-retry round trip.
Before implementing your functions, review the [C++ API Reference](/mysql-8.4/0.0.5/extension-api-reference) for the complete VDF contracts: null checking, result types, buffer sizing, and error handling.
## Step 4: Creating Custom Types
Custom types let you define new column types — like `COMPLEX`, `UUID`, or
`VECTOR` — that work with `ORDER BY`, indexes, and aggregate functions.
If your extension only registers functions, skip to Step 5.
See [Custom Types in C++](/mysql-8.4/0.0.5/custom-types) for the full
implementation walkthrough. If your type takes parameters (e.g., `VECTOR(1536)`),
see [Parameterized Types](/mysql-8.4/0.0.5/type-operations#parameterized-types).
## Step 5: Update Build Configuration
Edit `CMakeLists.txt` to build your extension as a VEB file:
```cmake theme={null}
cmake_minimum_required(VERSION 3.18)
project(my_extension)
# Find VillageSQL Extension Framework
find_package(VillageSQLExtensionFramework QUIET)
# 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 `mysql-test/t/`:
```sql theme={null}
-- mysql-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/mysql-test --record
```
3. **Run tests**:
```bash theme={null}
perl mysql-test-run.pl --suite=/path/to/your/extension/mysql-test
```
## 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.18+ 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
Argument and result types, aggregates, system variables, and testing
Lifecycle, caching, performance, and security model
Build VillageSQL from source code
## 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)