---
layout: default
title: "User-Defined Functions"
parent: Functions
nav_order: 5
---
# User-Defined Functions
Oxibase supports user-defined functions written in multiple scripting languages through pluggable backends. By default, functions can be written in Rhai (a lightweight, fast scripting language) and PL/SQL (a native procedural language clone), with optional support for Python (via RustPython). This allows you to extend the database with custom logic while choosing the right tool for each use case.
## Overview
User-defined functions (UDFs) enable you to create custom scalar functions that can be called from SQL queries. Functions run in secure, isolated environments with controlled access to system resources.
## Scripting Backends
Oxibase supports multiple scripting backends, each optimized for different use cases:
### Rhai Backend (Default)
- **Language**: `LANGUAGE RHAI`
- **Description**: Lightweight, fast scripting language written in Rust
- **Performance**: Excellent performance for simple calculations and logic
- **Availability**: Always enabled
- **Use Case**: General-purpose scripting, high-performance requirements
### Python Backend (Optional)
- **Language**: `LANGUAGE PYTHON`
- **Description**: Python scripting with access to standard library
- **Performance**: Good performance with extensive libraries
- **Availability**: Enable with `--features python`
- **Use Case**: Scientific computing, data processing, ML/AI integration
## Enabling Optional Backends
To use Python functions, enable the corresponding feature flags:
```bash
# Enable Python support
cargo build --features python
```
## Functions vs Stored Procedures
Oxibase supports both **user-defined functions** and **stored procedures**. Understanding the difference is important for choosing the right tool for your database logic.
### Comparison at a Glance
| **Return Value** | **Must** return exactly one value (scalar). | Can return zero, one, or multiple values. |
| **Usage in SQL** | Can be used in `SELECT`, `WHERE`, and `JOIN`. | Must be called using `EXECUTE` or `CALL`. |
| **Data Modification** | Cannot perform DML (Insert, Update, Delete). | Can perform any DML operations. |
| **Transactions** | **No** transaction control allowed. | Supports `COMMIT`, `ROLLBACK`, and `SAVEPOINT`. |
| **Parameters** | Generally only **Input** parameters. | Supports **Input**, **Output**, and **In-Out**. |
| **Error Handling** | Limited (script exceptions only). | Full support for error handling constructs. |
### Key Differences
#### Integration with Queries
Functions are "pluggable" into your SQL statements and can be used just like built-in functions:
```sql
-- Function usage in queries
SELECT calculate_tax(price) FROM products;
SELECT * FROM users WHERE is_adult(age);
```
Procedures cannot be used directly in queries and must be called separately using `CALL`:
```sql
-- Procedure usage
CALL update_inventory();
CALL process_monthly_report();
```
#### Side Effects and DML
Functions are restricted to be "side-effect free" and cannot change database state:
```sql
-- ✅ Valid function - read-only calculation
CREATE FUNCTION calculate_tax(price INTEGER) RETURNS INTEGER
LANGUAGE PYTHON AS 'return price * 0.08';
```
Procedures are designed for actions that modify data:
```sql
-- Valid procedure that modifies data
CREATE PROCEDURE update_prices()
LANGUAGE plsql AS $$
BEGIN
UPDATE products SET price = price * 1.1;
END;
$$;
```
#### When to Use Functions vs Procedures
**Use Functions when:**
- You need to perform calculations and use results in queries
- The logic is simple and read-only
- You want to encapsulate reusable business logic
- Examples: Currency conversion, string formatting, age calculation
**Use Procedures when:**
- You need to perform write operations (INSERT/UPDATE/DELETE)
- You need complex multi-step logic with error handling
- You need transaction control
- You need to return multiple result sets
- Examples: Monthly payroll processing, customer registration, data cleanup
## Creating User-Defined Functions
Use the `CREATE FUNCTION` statement to define a user-defined function:
```sql
CREATE FUNCTION function_name(param1 TYPE1, param2 TYPE2, ...)
RETURNS return_type
LANGUAGE PYTHON AS 'python code here';
```
### Multiline Code Blocks
For functions and procedures with larger bodies of code, escaping single quotes inside standard string literals can be tedious. Oxibase provides two additional ways to write multiline code blocks that apply to **both `CREATE FUNCTION` and `CREATE PROCEDURE`**:
**Postgres-style Dollar Quotes:**
You can wrap the code inside `$$` or tagged dollar quotes (like `$python$`). Single quotes inside the block do not need to be escaped.
```sql
CREATE FUNCTION format_currency(amount INTEGER) RETURNS TEXT
LANGUAGE PYTHON AS $$
# Simple formatting in python
return f"${amount / 100:.2f}"
$$;
```
**Markdown-style Triple Backticks:**
You can also use triple backticks (` ``` `), similar to Markdown code blocks, which makes it easy to copy-paste code snippets.
```sql
CREATE FUNCTION safe_divide(a INTEGER, b INTEGER) RETURNS FLOAT
LANGUAGE PYTHON AS ```
if b == 0:
raise Exception("Division by zero")
return a / b
```;
```
### Parameters
- `function_name`: The name of the function (must be unique)
- `param1, param2, ...`: Parameter names and their data types
- `return_type`: The data type of the return value
- `LANGUAGE RHAI|PYTHON`: Specifies the scripting backend to use
- `AS 'code'`: The code that implements the function
### Supported Return Data Types
User-defined functions can return values of these scalar data types:
| **`INTEGER`** | 64-bit signed integers | `return 42` |
| **`FLOAT`** | 64-bit floating-point numbers | `return 3.14159` |
| **`TEXT`** | UTF-8 text strings | `return "Hello World"` |
| **`BOOLEAN`** | True/false values | `return True` |
| **`TIMESTAMP`** | Date and time values | `return "2024-01-01T00:00:00Z"` |
| **`JSON`** | JSON documents and objects | `return {"name": "John", "age": 30}` |
Functions must return exactly one value and declare their return type in the `CREATE FUNCTION` statement. The script runtime automatically converts return values to the appropriate Oxibase type.
> **Note:** Oxibase currently only supports scalar user-defined functions. Table-valued functions are planned for future releases.
## Function Implementation
### Argument Access
All backends now support **named parameters**. Arguments are accessed by their parameter names:
#### Rhai Backend
Parameters are bound to variables with their declared names:
```sql
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'a + b';
```
#### Boa Backend
Parameters are set as global variables with their declared names:
```sql
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE BOA AS 'return a + b;';
```
#### Python Backend
Parameters are set as local variables with their declared names:
```sql
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return a + b';
```
Python functions support the same argument and return types as JavaScript:
- **INTEGER**: Python `int`
- **FLOAT**: Python `float`
- **TEXT**: Python `str`
- **BOOLEAN**: Python `bool`
- **JSON**: Python objects (dict/list parsed from JSON string)
Example functions:
```sql
-- String manipulation
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS 'return f"Hello, {name}!"';
-- Mathematical operations
CREATE FUNCTION power(base INTEGER, exp INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return base ** exp';
-- JSON processing
CREATE FUNCTION extract_field(json_data JSON, field TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS '''
import json
data = json.loads(json_data)
return data.get(field, "")
''';
```
### Return Values
Functions return values using backend-specific syntax:
#### Rhai Backend
```sql
-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE RHAI AS '"Hello, " + name + "!"';
-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'x * x';
-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE RHAI AS 'n % 2 == 0';
```
#### Boa Backend
```sql
-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE BOA AS 'return `Hello, ${name}!`;';
-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE BOA AS 'return x * x;';
-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE BOA AS 'return n % 2 === 0;';
-- Return JSON
CREATE FUNCTION create_person(name TEXT, age INTEGER)
RETURNS JSON
LANGUAGE BOA AS 'return { name: name, age: age };';
```
#### Python Backend
```sql
-- Return a string
CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE PYTHON AS 'return f"Hello, {name}!"';
-- Return a number
CREATE FUNCTION square(x INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON AS 'return x * x';
-- Return a boolean
CREATE FUNCTION is_even(n INTEGER)
RETURNS BOOLEAN
LANGUAGE PYTHON AS 'return n % 2 == 0';
-- Return JSON
CREATE FUNCTION create_person(name TEXT, age INTEGER)
RETURNS JSON
LANGUAGE PYTHON AS '''
import json
return json.dumps({"name": name, "age": age})
''';
```
**Note**: Python functions support both `return` statements (recommended) and direct `result =` assignments for backward compatibility. If no value is returned, the function returns `NULL`.
### JavaScript Features
User-defined functions have access to standard JavaScript features:
- All ECMAScript built-ins (Math, Date, etc.)
- Arrow functions and modern syntax
- Template literals
- Array and object methods
- JSON parsing and serialization
```sql
CREATE FUNCTION format_currency(amount INTEGER, currency TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
const formatted = new Intl.NumberFormat("en-US", {
style: "currency",
currency: currency
}).format(amount / 100);
return formatted;
';
```
## Using User-Defined Functions
Once created, user-defined functions can be used in any SQL context where scalar functions are allowed:
```sql
-- Simple usage
SELECT greet('World') as greeting;
-- In expressions
SELECT id, square(price) as price_squared
FROM products;
-- In WHERE clauses
SELECT * FROM users
WHERE is_even(age);
-- In complex queries
SELECT
name,
format_currency(salary, 'USD') as formatted_salary
FROM employees;
```
## Security Considerations
All backends execute in secure sandboxes with controlled access:
### Rhai Backend
- **No file system access** - Pure computation only
- **No network access** - Cannot make HTTP requests
- **Memory isolation** - Each call runs in isolated context
- **Limited runtime** - Execution time limits prevent abuse
### Boa Backend
- **No file system access** - Cannot read or write files
- **No network access** - Cannot make HTTP requests or open sockets
- **No system access** - Cannot execute system commands or access environment variables
- **Limited runtime** - Functions have execution time limits to prevent abuse
- **Memory isolation** - Each function call runs in its own JavaScript context
### Python Backend
- **No file system access** - Cannot read or write files
- **No network access** - Cannot make HTTP requests
- **Limited system access** - Cannot execute system commands
- **Memory isolation** - Each call runs in isolated context
- **Limited runtime** - Execution time limits prevent abuse
## Performance Characteristics
Performance varies by backend:
### Rhai Backend
- **Runtime Creation**: Minimal overhead (microseconds)
- **Execution Speed**: Fastest for simple calculations
- **Memory Usage**: Low memory footprint
- **Best For**: High-frequency calls, simple logic
### Boa Backend
- **Runtime Creation**: Moderate overhead (~milliseconds)
- **Execution Speed**: Good for complex logic
- **Memory Usage**: Higher memory usage
- **Best For**: Complex algorithms, JSON processing
### Python Backend
- **Runtime Creation**: Moderate overhead (~milliseconds)
- **Execution Speed**: Good for numerical computing
- **Memory Usage**: Moderate memory usage
- **Best For**: Scientific computing, data processing
### General Guidelines
- **Rhai** is recommended for most use cases due to its speed and low overhead
- Each function call creates a new runtime instance for isolation
- Consider caching results at the application level for expensive operations
- Runtime creation overhead is typically acceptable for OLTP workloads
## Examples
### Basic Arithmetic (Rhai)
```sql
CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'a + b';
CREATE FUNCTION calculate_area(width INTEGER, height INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS 'width * height';
SELECT add_numbers(5, 3) as sum, calculate_area(10, 20) as area;
-- Result: sum = 8, area = 200
```
### String Processing (Rhai)
```sql
CREATE FUNCTION slugify(text TEXT)
RETURNS TEXT
LANGUAGE RHAI AS '
text
.to_lower()
.replace(re("[^a-z0-9]+"), "-")
.replace(re("^-+|-+$"), "")
';
SELECT slugify('Hello, World! How are you?') as slug;
-- Result: "hello-world-how-are-you"
```
### String Processing (Boa)
```sql
CREATE FUNCTION slugify(text TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
return text
.toLowerCase()
.replace(/[^a-z0-9]+/g, "-")
.replace(/^-+|-+$/g, "");
';
SELECT slugify('Hello, World! How are you?') as slug;
-- Result: "hello-world-how-are-you"
```
### Date Calculations (Rhai)
```sql
CREATE FUNCTION days_until(date TEXT)
RETURNS INTEGER
LANGUAGE RHAI AS '
// Simple date difference calculation
// Note: Rhai has limited date support, consider Boa for complex date operations
30 // Placeholder - use Boa for real date calculations
';
// For complex date operations, use Boa:
CREATE FUNCTION days_until(date TIMESTAMP)
RETURNS INTEGER
LANGUAGE BOA AS '
const target = new Date(date);
const now = new Date();
const diff = target - now;
return Math.ceil(diff / (1000 * 60 * 60 * 24));
';
SELECT days_until('2024-12-31') as days_remaining;
```
### JSON Processing (Boa)
```sql
CREATE FUNCTION extract_field(json_doc JSON, field TEXT)
RETURNS TEXT
LANGUAGE BOA AS '
const doc = JSON.parse(json_doc);
return doc[field] || null;
';
SELECT extract_field(metadata, 'version') as version
FROM documents;
```
### Mathematical Functions (Rhai)
```sql
CREATE FUNCTION fibonacci(n INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS '
if n <= 1 { n }
else {
let a = 0;
let b = 1;
for i in 2..=n {
let temp = a + b;
a = b;
b = temp;
}
b
}
';
CREATE FUNCTION factorial(n INTEGER)
RETURNS INTEGER
LANGUAGE RHAI AS '
if n <= 1 { 1 }
else { n * factorial(n - 1) }
';
SELECT fibonacci(10) as fib, factorial(5) as fact;
-- Result: fib = 55, fact = 120
```
### Data Validation (Boa)
```sql
CREATE FUNCTION validate_email(email TEXT)
RETURNS BOOLEAN
LANGUAGE BOA AS '
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
return emailRegex.test(email);
';
CREATE FUNCTION is_strong_password(password TEXT)
RETURNS BOOLEAN
LANGUAGE BOA AS '
// At least 8 characters, 1 uppercase, 1 lowercase, 1 number
const strongRegex = /^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)[a-zA-Z\d]{8,}$/;
return strongRegex.test(password);
';
SELECT validate_email('user@example.com') as valid_email,
is_strong_password('MyPass123') as strong_password;
```
## Error Handling
Functions that throw exceptions will cause the query to fail:
```sql
-- Rhai
CREATE FUNCTION safe_divide(a INTEGER, b INTEGER)
RETURNS FLOAT
LANGUAGE RHAI AS '
if b == 0 {
throw "Division by zero";
}
a / b
';
```
## Backend-Specific Considerations
### Rhai Backend
- **Syntax**: Simple, Rust-like syntax with automatic type inference
- **Features**: Basic arithmetic, string operations, conditionals, loops
- **Limitations**: Limited standard library, no built-in JSON parsing
- **Performance**: Excellent for numerical computations and simple logic
### Python Backend
- **Syntax**: Standard Python syntax
- **Features**: Extensive standard library, good for numerical computing
- **Limitations**: Moderate startup time, higher memory usage
- **Performance**: Good for algorithms requiring complex data structures
## Limitations
- Only scalar functions are supported (not aggregate or window functions)
- Functions cannot access database state directly
- Maximum execution time per function call is limited
- Memory usage per function is bounded
- No access to external modules or packages (backend-specific limitations apply)
- Rhai has a smaller standard library compared to Python
## Best Practices
1. **Choose the right backend**:
- Use **Rhai** for simple, high-performance calculations
- Use **Python** for scientific computing or when you need extensive libraries
2. **Keep functions simple** - Complex logic is better handled in application code
3. **Validate inputs** - Functions should handle edge cases and invalid inputs gracefully
4. **Use appropriate return types** - Match the function's purpose with the correct data type
5. **Test thoroughly** - User-defined functions should be well-tested across all backends
6. **Consider performance** - Rhai is fastest, but choose based on your specific needs
7. **Document your functions** - Use meaningful names and consider adding comments
8. **Handle errors appropriately** - Different backends have different error handling patterns
## UDF Testing Strategy
User-defined functions (UDFs) require comprehensive testing to ensure reliability across different backends and environments. The testing strategy covers multiple layers and scenarios.
### Test Categories
#### 1. Unit Tests per Backend
Each backend has dedicated test suites that validate:
- **Function Creation**: Syntax validation and compilation
- **Argument Handling**: Type conversion and parameter passing
- **Return Values**: Correct output types and values
- **Error Handling**: Runtime errors and exception propagation
- **Type Safety**: Mixed-type operations and conversions
**Examples:**
```rust
// Rhai backend tests
#[test]
fn test_rhai_string_manipulation() {
let db = Database::open("memory://test").unwrap();
db.execute(r#"
CREATE FUNCTION greet(name TEXT) RETURNS TEXT
LANGUAGE RHAI AS '`Hello, ${name}!`'
"#, ()).unwrap();
// ... validation
}
// Python backend tests
#[test]
fn test_python_runtime_error() {
let db = Database::open("memory://test").unwrap();
db.execute(r#"
CREATE FUNCTION error_func() RETURNS INTEGER
LANGUAGE PYTHON AS 'return 1 / 0'
"#, ()).unwrap();
// ... error validation
}
```
#### 2. Cross-Backend Integration Tests
Tests that verify consistent behavior across all enabled backends:
- **Function Registration**: Multi-backend function loading
- **Execution Consistency**: Same inputs produce equivalent outputs
- **Error Message Alignment**: Standardized error formats
- **Performance Validation**: Backend-specific performance expectations
#### 3. End-to-End SQL Tests
Complete SQL workflow testing including:
- **DDL Operations**: CREATE/DROP FUNCTION statements
- **Query Integration**: UDFs in SELECT, WHERE, and aggregate contexts
- **Transaction Safety**: UDF behavior within transactions
- **Concurrency**: Multi-user function execution
#### 4. Environment Consistency Tests
Validates UDF reliability across different environments:
- **Feature Flags**: Tests with different cargo feature combinations
- **Platform Compatibility**: Cross-platform execution consistency
- **Dependency Isolation**: Sandboxing and security boundary validation
### Backend-Specific Considerations
#### Rhai Backend
- **String Concatenation**: Requires explicit `.to_string()` for non-string types
- **Type Coercion**: Limited implicit conversions
- **Performance**: Fastest for simple operations
- **Error Format**: "Rhai execution error: {message}"
#### Python Backend
- **Library Access**: Extensive standard library and scientific computing
- **Type Flexibility**: Dynamic typing with runtime conversion
- **Error Format**: "Python execution error: {exception_string}"
- **Security**: Isolated execution environment
### Testing Infrastructure
#### Test Organization
```
tests/
├── functions/
│ ├── backends_test.rs # Backend registration and loading
│ └── ...
├── rhai_scripting_test.rs # Rhai-specific functionality
├── python_scripting_test.rs # Python-specific functionality
├── multi_backend_integration_test.rs # Cross-backend scenarios
└── ddl_function_test.rs # DDL operations
```
#### CI/CD Integration
- **Matrix Testing**: All backends tested with `--all-features`
- **Feature Isolation**: Individual backend testing with specific features
- **Performance Regression**: Execution time monitoring
- **Memory Safety**: Leak detection and resource cleanup
#### Test Execution
```bash
# Run all tests with all backends
make test-all
# Run specific backend tests
cargo test --test rhai_scripting_test --features rhai
cargo test --test python_scripting_test --features python
# Run integration tests
cargo test --test multi_backend_integration_test --all-features
```
### Best Practices for UDF Testing
1. **Test All Backends**: Ensure UDFs work across Rhai and Python
2. **Validate Error Messages**: Check error format consistency
3. **Performance Benchmarking**: Compare execution times across backends
4. **Type Edge Cases**: Test with null values, type mismatches, and boundaries
5. **Concurrency Testing**: Multiple UDF executions in parallel
6. **Memory Leak Detection**: Ensure proper resource cleanup
7. **Cross-Platform Validation**: Test on different operating systems
8. **Security Boundary Testing**: Validate sandbox isolation
### Common Testing Patterns
#### Error Testing
```rust
let result: Result<i64, _> = db.query_one("SELECT error_func()", ());
assert!(result.is_err());
let err_msg = result.unwrap_err().to_string();
// Check for backend-specific error patterns
assert!(err_msg.contains("execution error"));
```
#### Type Conversion Testing
```rust
// Test string concatenation with mixed types
db.execute(r#"
CREATE FUNCTION format_person(name TEXT, age INTEGER)
RETURNS TEXT LANGUAGE RHAI AS 'name + " is " + age.to_string() + " years old"'
"#, ()).unwrap();
```
#### Performance Validation
```rust
let start = std::time::Instant::now();
// Execute UDF multiple times
for _ in 0..1000 {
db.query_one("SELECT fast_udf(42)", ()).unwrap();
}
let duration = start.elapsed();
// Assert performance expectations
assert!(duration < std::time::Duration::from_millis(100));
```
## Dropping Functions
User-defined functions can be dropped using the `DROP FUNCTION` statement:
```sql
DROP FUNCTION function_name;
```
### Parameters
- `function_name`: The name of the function to drop
- `IF EXISTS`: Optional clause that prevents an error if the function doesn't exist
### Examples
```sql
-- Drop a function
DROP FUNCTION calculate_total;
-- Drop a function only if it exists
DROP FUNCTION IF EXISTS old_function;
-- Drop a schema-qualified function
DROP FUNCTION myschema.add_numbers;
```
### Behavior
- Dropping a function removes it from the database permanently
- The function becomes unavailable for new queries immediately
- Existing queries using the function may fail if the function is dropped during execution
- Functions are dropped from both the system catalog and the runtime registry