// ... 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-9.7/dev/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()`, never write
past `buffer().size()` — that overflows memory. But do not fail on a value that is
too large either: report the size you would have written and let the server grow
the buffer and call you again.
```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()) {
// Too small this time. Report what is needed and return: the server
// grows the buffer to `needed` and re-invokes this function, and the
// second call takes the branch below.
out.set_length(needed);
return;
}
// Write output into buf.data()
out.set_length(actual_output_length);
}
```
For STRING results, `out.set(sv)` follows an snprintf-style overflow contract: it
copies as many bytes as fit in `out.buffer()`, but reports the value's *full*
size. The server detects that the reported length exceeds the buffer, grows the
buffer, and calls the function a second time — so a value larger than
`.buffer_size()` is returned whole rather than truncated. One retry always
suffices, because the reported length says exactly how large the buffer must be.
Growth is bounded by `max_allowed_packet`. A value larger than that is returned
as `NULL`, not truncated.
Writing into `out.buffer()` and calling `out.set_length()` yourself participates
in the same contract: report the full size you would have written, not the number
of bytes that fit. Reporting the truncated length defeats the retry and silently
drops the remainder.
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](/docs/mysql-9.7/dev/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++](/docs/mysql-9.7/dev/custom-types) for the full
implementation walkthrough. If your type takes parameters (e.g., `VECTOR(1536)`),
see [Parameterized Types](/docs/mysql-9.7/dev/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 .. -DCMAKE_BUILD_TYPE=RelWithDebInfo
# Or, if building against VillageSQL source:
cmake .. -DCMAKE_BUILD_TYPE=RelWithDebInfo -DVillageSQL_BUILD_DIR=/path/to/villagesql/build
# Or, to build against the unstable dev ABI headers:
cmake .. -DCMAKE_BUILD_TYPE=RelWithDebInfo -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/9.7/en/adding-loadable-function.html)
* [CMake Documentation](https://cmake.org/documentation/)
* [VillageSQL Community Discord](https://discord.gg/KSr6whd3Fr)