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
    normalized_quarter := 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))"
        }
    }'
    )
SELECT
    quarter_from_date := NORMALIZED_QUARTER(DATE '2025-08-15'),
    quarter_from_timestamp := TIMESTAMP '2025-10-15 14:30:00'.NORMALIZED_QUARTER() -- 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
    date_input := '2025-08-15'::DATE,
    quarter_from_date := DATE_PART(date_input, 'YEAR')::VARCHAR || 'Q'
    || DATE_PART(date_input, 'QUARTER')::VARCHAR
SELECT
    quarter_from_date
;

Or as a macro, which adds reusability:

WITH
    date_input := INPUT(DATE),
    normalized_quarter := MACRO(DATE_PART(date_input, 'YEAR')::VARCHAR || 'Q'
    || DATE_PART(date_input, 'QUARTER')::VARCHAR USING 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"()
;

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.