Skip to main content
Markdown

PostgreSQL Server Functions

Compile typed Codon functions into a native PostgreSQL library with matching SQL declarations. The SDK maps ordinary numbers, text, bytes, and optional values to PostgreSQL's calling interface, leaving the calculation in application code.

Server functions suit bounded transformations that belong inside a query. PostgreSQL loads the library into each backend that uses it; installation requires a database superuser and a build matching the server's major version.

Before you begin: install the all-SDK distribution and use a development PostgreSQL server with matching server headers and pg_config. You will build a shared library and register three SQL functions.

Write Your First Functions​

Save these functions as functions.codon:

functions.codon
import exaloop.databases.postgres as postgres


@postgres.function(volatility="immutable")
def add(left: int, right: int) -> int:
    return left + right


@postgres.function(volatility="immutable")
def greeting(name: Optional[str]) -> str:
    return "Hello, " + ("world" if name is None else name) + "!"


@postgres.function(volatility="immutable")
def safe_ratio(numerator: float, denominator: float) -> Optional[float]:
    if denominator == 0.0:
        return None
    return numerator / denominator

Each argument and return value has an explicit type. The decorator marks a top-level function for export; undecorated helpers remain ordinary Codon functions. For these functions in functions.codon, use your installed SDK to build the library and install its declarations in a development database:

Terminal
exaloop --kind postgres-udf build functions.codon -o "$PWD/build/codon_functions.so"
psql -X -v ON_ERROR_STOP=1 -d your_database -f build/codon_functions.sql

Replace your_database with your development database name.

Use the SDK builder directly
Terminal
python3 /path/to/sdk/postgres/build.py functions.codon \
  --codon /absolute/path/to/codon -o "$PWD/build/codon_functions.so"

For a development compiler, also pass --stdlib /absolute/path/to/codon/stdlib.

Compiling functions requires Codon 0.20.3, Python 3.9+, a C compiler, and the PostgreSQL server headers and pg_config selected by the SDK. Use an SDK matching your server's major version; PostgreSQL extensions must be rebuilt for each major version. The server and its development files should come from the same installation.

The database server must be able to read the library and its runtime dependencies. The generated script uses an absolute library path and creates functions in public; --schema app selects another existing schema.

Now use the functions in SQL:

SQL
SELECT public.add(19, 23);                  -- 42
SELECT public.greeting(NULL);              -- Hello, world!
SELECT public.safe_ratio(3.0, 2.0);         -- 1.5
SELECT public.safe_ratio(1.0, 0.0) IS NULL; -- true

Exported functions need explicit argument and return types. Keep the export file Python-compatible and put Codon-specific constructs in imported helpers.

Types and NULLs​

Codon annotationPostgreSQL type
boolboolean
i16smallint
i32integer
int, i64bigint
float32real
floatdouble precision
strtext
bytesbytea
Optional[T]The same SQL type, with explicit NULL handling

In the example, greeting accepts Optional[str] so it can replace a missing name with world. The optional return type on safe_ratio lets it return SQL NULL when a ratio is undefined. Without an optional argument, a NULL input passes through as NULL without calling your function.

Use str for UTF-8 text and bytes for binary content. Let the builder generate the SQL declarations so their types stay consistent with your code.

Planner Settings​

The example marks its functions immutable because their answers depend only on their arguments. That lets PostgreSQL reuse or precompute results. Leave the default volatile setting when a function reads time, configuration, or mutable state. Other decorator options are listed in the function reference.

Errors and Backend State​

An uncaught exception becomes a PostgreSQL error and follows normal transaction rollback rules. A long-running native callback can delay query cancellation, so short functions keep cancellation responsive. PostgreSQL tables are the appropriate place for durable state: module globals are private to a backend and are not rolled back with a transaction.

Next Steps​