Python user-defined functions (UDF)

chDB allows you to register Python functions as SQL-callable UDFs. These run natively in-process — no subprocess spawning, no serialization overhead. Functions are type-safe, support automatic type inference from Python annotations, and offer configurable NULL and exception handling.

Quick start

from chdb import query, func
from chdb.sqltypes import INT64

@func([INT64, INT64], INT64)
def add(a, b):
    return a + b

result = query("SELECT add(2, 3)")
print(result)  # 5

Examples in this guide run query() with the default CSV output format. Inline comments show the logical result values; the raw output prints NULL as \N and applies CSV quoting to string and date values (for example "Hello, world!").

Registration methods

`@func` decorator

The simplest way to register a UDF. The function’s __name__ becomes the SQL function name.

from chdb import func
from chdb.sqltypes import INT64, STRING

# Explicit types
@func([INT64, INT64], INT64)
def add(a, b):
    return a + b

# Types inferred from annotations
@func()
def multiply(a: int, b: int) -> int:
    return a * b

# Explicit return_type, arg_types inferred from annotations
@func(return_type=STRING)
def greet(name: str):
    return f"Hello, {name}!"

The decorated function remains callable as normal Python:

add(2, 3)       # 5 (Python call)
query("SELECT add(2, 3)")  # 5 (SQL call)

`create_function`

from chdb import create_function, query
from chdb.sqltypes import INT64, STRING

create_function("strlen", len, arg_types=[STRING], return_type=INT64)
query("SELECT strlen('hello')")  # 5

create_function("double", lambda x: x * 2, arg_types=[INT64], return_type=INT64)
query("SELECT double(21)")  # 42

`drop_function`

Remove a registered UDF. Dropping a name that is not registered does nothing, so it is safe to call unconditionally:

from chdb import drop_function

drop_function("strlen")
# query("SELECT strlen('hello')")  # Error: function not found

Registering a name that is already registered raises an error — UDFs are not silently replaced. Call drop_function(name) first to re-register a function, for example when re-running a notebook cell.

Type system

Available types

All types are importable from chdb.sqltypes:

from chdb.sqltypes import (
    # Boolean
    BOOL,
    # Signed integers
    INT8, INT16, INT32, INT64, INT128, INT256,
    # Unsigned integers
    UINT8, UINT16, UINT32, UINT64, UINT128, UINT256,
    # Floating point
    FLOAT32, FLOAT64,
    # String
    STRING,
    # Date and time
    DATE, DATE32, DATETIME, DATETIME64,
)

Specifying types

Types can be provided in four ways:

Method	Example	Description
`ChdbType` constant	`INT64`, `STRING`	Imported from `chdb.sqltypes`
ClickHouse type string	`"Int64"`, `"String"`	Standard ClickHouse type names
Parameterized string	`"DateTime('UTC')"`, `"DateTime64(6)"`	For types with parameters
Python type	`int`, `str`, `float`	Passed directly in `arg_types`/`return_type`, or used as type annotations in the function signature

from chdb import create_function, func
from chdb.sqltypes import INT64

# All equivalent:
create_function("f1", lambda x: x * 2, arg_types=[INT64], return_type=INT64)
create_function("f2", lambda x: x * 2, arg_types=["Int64"], return_type="Int64")
create_function("f3", lambda x: x * 2, arg_types=[int], return_type=int)

@func()
def f4(x: int) -> int:
    return x * 2

Automatic type inference

When arg_types or return_type is omitted, chDB infers types from Python type annotations:

Python Type	ClickHouse Type
`bool`	`Bool`
`int`	`Int64`
`float`	`Float64`
`str`	`String`
`bytes`	`String`
`bytearray`	`String`
`datetime.date`	`Date`
`datetime.datetime`	`DateTime64(6)`

@func()
def process(name: str, age: int) -> str:
    return f"{name} is {age} years old"

# Equivalent to:
# @func([STRING, INT64], STRING)

If arg_types is provided explicitly, it must cover all parameters — partial explicit + partial inferred is not supported. This applies to both create_function and the @func decorator: either specify types for all parameters, or omit them entirely and let chDB infer from annotations.

A return type is always required: if return_type is omitted and the function has no return annotation, registration fails. Argument types, by contrast, are optional — a parameter with neither an explicit type nor an annotation accepts any supported input type dynamically.

NULL handling

The on_null parameter controls behavior when any input argument is NULL.

Value	Behavior
`"skip"` (default)	Return NULL immediately without calling the function
`"pass"`	Convert NULL to Python `None` and call the function normally

You can also use the enum: chdb.NullHandling.SKIP / chdb.NullHandling.PASS.

Example: default (skip)

@func(return_type="Int64")
def increment(x: int) -> int:
    return x + 1

query("SELECT increment(NULL)")  # NULL
query("SELECT increment(5)")     # 6

Example: pass NULL as `None`

@func(return_type="Int64", on_null="pass")
def null_to_zero(x):
    return 0 if x is None else x + 1

query("SELECT null_to_zero(NULL)")  # 0
query("SELECT null_to_zero(5)")     # 6

Example: multiple arguments

@func(arg_types=["Int64", "Int64"], return_type="Int64", on_null="pass")
def add_or_zero(a, b):
    return (a or 0) + (b or 0)

query("SELECT add_or_zero(NULL, 5)")    # 5
query("SELECT add_or_zero(NULL, NULL)") # 0
query("SELECT add_or_zero(3, 7)")       # 10

Exception handling

The on_error parameter controls behavior when the Python function raises an exception.

Value	Behavior
`"propagate"` (default)	Raise the exception as a SQL error
`"ignore"`	Catch the exception and return NULL for that row

You can also use the enum: chdb.ExceptionHandling.PROPAGATE / chdb.ExceptionHandling.IGNORE.

Example: default (propagate)

@func(arg_types=["Int64", "Int64"], return_type="Int64")
def divide(a, b):
    return a // b

query("SELECT divide(10, 2)")  # 5
query("SELECT divide(1, 0)")   # Error: ZeroDivisionError

Example: ignore errors

@func(arg_types=["Int64", "Int64"], return_type="Int64", on_error="ignore")
def safe_divide(a, b):
    return a // b

query("SELECT safe_divide(10, 2)")  # 5
query("SELECT safe_divide(1, 0)")   # NULL

Combining NULL and exception handling

The on_null and on_error options can be combined:

on_null	on_error	NULL input	Exception
`"skip"`	`"propagate"`	Return NULL	Raise error
`"skip"`	`"ignore"`	Return NULL	Return NULL
`"pass"`	`"propagate"`	Call with `None`	Raise error
`"pass"`	`"ignore"`	Call with `None`	Return NULL

@func(
    arg_types=["Int64", "Int64"],
    return_type="Int64",
    on_null="pass",
    on_error="ignore",
)
def robust_divide(a, b):
    if a is None or b is None:
        return -1
    return a // b

query("SELECT robust_divide(10, 2)")     # 5
query("SELECT robust_divide(NULL, 2)")   # -1
query("SELECT robust_divide(1, 0)")      # NULL (exception caught)

DateTime and timezone support

UDFs fully support date and time types with timezone awareness.

Date types

from datetime import date, timedelta

@func()
def next_day(d: date) -> date:
    return d + timedelta(days=1)

@func()
def get_year(d: date) -> int:
    return d.year

query("SELECT next_day(toDate('2024-06-15'))")  # 2024-06-16
query("SELECT get_year(toDate('2024-06-15'))")  # 2024

DateTime with timezones

from datetime import timedelta

@func(arg_types=["DateTime('UTC')"], return_type="DateTime('UTC')")
def add_one_hour(dt):
    return dt + timedelta(hours=1)

query("SELECT add_one_hour(toDateTime('2024-01-01 12:00:00', 'UTC'))")  # 2024-01-01 13:00:00

DateTime64 (high precision)

DATETIME64 defaults to scale 6 (microseconds):

from datetime import timedelta

@func(arg_types=["DateTime64(6, 'UTC')"], return_type="DateTime64(6, 'UTC')")
def add_microsecond(dt):
    return dt + timedelta(microseconds=1)

query("SELECT add_microsecond(toDateTime64('2024-01-01 12:00:00.000000', 6, 'UTC'))")  # 2024-01-01 12:00:00.000001

Input DateTime/DateTime64 values carry timezone info from ClickHouse
Output datetime objects preserve timezone info
Timezone conversion is handled automatically

Using UDFs with sessions

UDFs are registered globally and available across all sessions in the same process:

from chdb import session as chs, func
from chdb.sqltypes import INT64

@func([INT64], INT64)
def double(x):
    return x * 2

sess = chs.Session()
sess.query("CREATE TABLE t (x Int64) ENGINE = Memory")
sess.query("INSERT INTO t VALUES (1), (2), (3)")
result = sess.query("SELECT double(x) FROM t ORDER BY x", "CSV")
print(result)
# 2
# 4
# 6

Get started

Language integrations

DataStore

Configuration

Debugging

Developer guides

Technical reference

Quick start

Registration methods

`@func` decorator

`create_function`

`drop_function`

Type system

Available types

Specifying types

Automatic type inference

NULL handling

Example: default (skip)

Example: pass NULL as `None`

Example: multiple arguments

Exception handling

Example: default (propagate)

Example: ignore errors

Combining NULL and exception handling

DateTime and timezone support

Date types

DateTime with timezones

DateTime64 (high precision)

Using UDFs with sessions

​Quick start

​Registration methods

​@func decorator

​create_function

​drop_function

​Type system

​Available types

​Specifying types

​Automatic type inference

​NULL handling

​Example: default (skip)

​Example: pass NULL as None

​Example: multiple arguments

​Exception handling

​Example: default (propagate)

​Example: ignore errors

​Combining NULL and exception handling

​DateTime and timezone support

​Date types

​DateTime with timezones

​DateTime64 (high precision)

​Using UDFs with sessions

Quick start

Registration methods

`@func` decorator

`create_function`

`drop_function`

Type system

Available types

Specifying types

Automatic type inference

NULL handling

Example: default (skip)

Example: pass NULL as `None`

Example: multiple arguments

Exception handling

Example: default (propagate)

Example: ignore errors

Combining NULL and exception handling

DateTime and timezone support

Date types

DateTime with timezones

DateTime64 (high precision)

Using UDFs with sessions