-   [](https://platform.exaloop.io/)
-   Databases
-   [PostgreSQL](https://platform.exaloop.io/db/postgres/)
-   Server functions

# 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](https://platform.exaloop.io/start/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`:

```python title="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:

```sh title="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

```sh title="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 title="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 annotation | PostgreSQL type |
| --- | --- |
| `bool` | `boolean` |
| `i16` | `smallint` |
| `i32` | `integer` |
| `int`, `i64` | `bigint` |
| `float32` | `real` |
| `float` | `double precision` |
| `str` | `text` |
| `bytes` | `bytea` |
| `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](https://platform.exaloop.io/db/reference/#function-options-1).

## 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

-   [Package and deploy the functions](https://platform.exaloop.io/db/postgres/deployment/) as a PostgreSQL extension.
-   Review [function options](https://platform.exaloop.io/db/reference/#function-options-1) before changing planner settings.
-   [Use the native client](https://platform.exaloop.io/db/postgres/client/) for calculations that should run outside the server.

Source: https://platform.exaloop.io/db/postgres/functions/