User-defined functions

User-Defined Functions (UDFs) let you extend FeatureQL with custom functions implemented in the target SQL dialect. Unlike macros , UDFs support polymorphism — the same function name can accept different input types and produce different output types.

Defining a UDF

A UDF is defined as a JSON configuration passed to the UDF() function. The configuration has three required fields:

• positions — maps positional arguments to named parameters
• signatures — defines the supported type combinations (input types → output type)
• templates — provides the SQL template for each target backend, using Jinja2 syntax

Here's a normalized_quarter() function that accepts either a DATE or TIMESTAMP and returns a string like "2025Q3":

WITH
    UDF(JSON '{
        "positions": ["date_input"],
        "signatures": [
            ["DATE", "VARCHAR"],
            ["TIMESTAMP", "VARCHAR"]
        ],
        "templates": {
            "FEATUREQL": "NORMALIZED_QUARTER({{ jexpr(p(''date_input'')) }})",
            "SQL": "CONCAT(CAST(YEAR({{ jexpr(p(''date_input'')) }}) AS VARCHAR), ''Q'', CAST(QUARTER({{ jexpr(p(''date_input'')) }}) AS VARCHAR))"
        }
    }') AS normalized_quarter
SELECT
    normalized_quarter(DATE '2025-08-15') as QUARTER_FROM_DATE,
    TIMESTAMP '2025-10-15 14:30:00'.normalized_quarter() as QUARTER_FROM_TIMESTAMP  -- also works with chaining syntax
;

The function works with both types thanks to the two signatures. It also supports chaining syntax — TIMESTAMP '...'.normalized_quarter() is equivalent to normalized_quarter(TIMESTAMP '...').

When to use native features instead

Most custom logic doesn't need a UDF. If your function only handles one input type, a regular feature or macro does the same job with simpler syntax.

The same quarter calculation as a plain feature:

WITH
    '2025-08-15'::DATE AS DATE_INPUT,
    EXTRACT_FROM_DATE(DATE_INPUT, 'YEAR')::VARCHAR || 'Q' || EXTRACT_FROM_DATE(DATE_INPUT, 'QUARTER')::VARCHAR AS QUARTER_FROM_DATE
SELECT
    QUARTER_FROM_DATE
;

Or as a macro, which adds reusability:

WITH
    DATE_INPUT := INPUT(DATE),
    NORMALIZED_QUARTER := MACRO(
        EXTRACT_FROM_DATE(DATE_INPUT, 'YEAR')::VARCHAR || 'Q' || EXTRACT_FROM_DATE(DATE_INPUT, 'QUARTER')::VARCHAR  -- TODO: Arrays not supported yet
        USING INPUTS DATE_INPUT
    )
SELECT
    QUARTER_FROM_DATE := NORMALIZED_QUARTER(DATE '2025-08-15'),
    QUARTER_FROM_TIMESTAMP := TIMESTAMP '2025-10-15 14:30:00'::DATE.NORMALIZED_QUARTER()  -- cast TIMESTAMP as DATE first
;

The macro version handles one type (DATE), so the timestamp input needs an explicit cast. A UDF avoids this by declaring both signatures.

Use a UDF when you need polymorphism (multiple input types), backend-specific SQL, or a function that other teams can call without knowing the implementation. Use a macro for everything else — it's simpler and doesn't require JSON configuration.