User-defined functions

User-Defined Functions (UDFs) let you extend FeatureQL with custom polymorphic scalar functions whose bodies are plain SQL expressions. Unlike macros , a UDF can declare several SIGNATURE overloads and dialect-specific SQL implementations.

Defining a UDF

A UDF declares one or more SIGNATURE overloads and at least one SQL implementation:

• SIGNATURE (param TYPE, ...) RETURNS TYPE — parameter names, parameter types, and the return type. Every signature must use the same parameter names and arity; only the types may differ.
• IMPLEMENTED AS <dialect> SQL (...) — required for each backend that should run the UDF. There is no default fallback and no cross-dialect rewrite of user SQL: a missing body for the active backend raises UE/UDF-NOT-SUPPORTED-DIALECT.

Allowed dialects today: DUCKDB, TRINO, BIGQUERY, DATAFUSION.

DEFAULT IMPLEMENTED AS remains in the grammar only so the parser can reject it cleanly (UE/UDF-DEFAULT-NOT-ALLOWED). Do not use it.

Parameter names inside the SQL body stand for the declared signature parameters. At SQL generation time they are replaced with the call-site argument expression trees — write dialect-native SQL such as YEAR(DATE_INPUT) or EXTRACT(YEAR FROM DATE_INPUT), not a FeatureQL helper call.

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

WITH
    normalized_quarter := UDF(
        SIGNATURE (DATE_INPUT DATE) RETURNS VARCHAR,
        SIGNATURE (DATE_INPUT TIMESTAMP) RETURNS VARCHAR
        IMPLEMENTED AS DUCKDB SQL (
            CONCAT(
                CAST(YEAR(DATE_INPUT) AS VARCHAR),
                'Q',
                CAST(QUARTER(DATE_INPUT) AS VARCHAR)
            )
        )
        IMPLEMENTED AS TRINO SQL (
            CONCAT(
                CAST(YEAR(DATE_INPUT) AS VARCHAR),
                'Q',
                CAST(QUARTER(DATE_INPUT) AS VARCHAR)
            )
        )
        IMPLEMENTED AS DATAFUSION SQL (
            CONCAT(
                CAST(EXTRACT(YEAR FROM DATE_INPUT) AS VARCHAR),
                'Q',
                CAST(EXTRACT(QUARTER FROM 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 two signatures make both call sites type-check. Chaining works the same as for built-ins — TIMESTAMP '...'.normalized_quarter() is equivalent to normalized_quarter(TIMESTAMP '...').

SQL body rules

The body is ordinary scalar SQL for the dialect you name, not FeatureQL and not a template language:

• Use the dialect’s functions and operators (CONCAT, YEAR, EXTRACT, LEN, STRUCT_EXTRACT, …).
• Refer only to declared parameter names (and field access on row/struct parameters, e.g. R.a where the dialect allows it).
• Keep the expression scalar: no SELECT statements, subqueries, CTEs, tables, or joins.

Complex types are fine when the SQL is scalar — for example ARRAY<…> with LEN / LIST_TRANSFORM, or ROW<…> with struct field access — as long as each signature spells the types and each dialect body uses that dialect’s spelling.

Current limits

These limits match the implementation today; richer template helpers are not available in UDF bodies:

If you need type-driven emission or structural SQL, keep the logic in FeatureQL / a macro , or split overloads into separate signatures with different SQL bodies.

Dialect-specific SQL

When engines disagree on function names or struct access, declare one IMPLEMENTED AS body per backend. The body for the active backend wins:

WITH
    DIALECT_MARKER := UDF(
        SIGNATURE (X BIGINT) RETURNS BIGINT
        IMPLEMENTED AS DUCKDB SQL (
            X * 1
        )
        IMPLEMENTED AS TRINO SQL (
            X * 2
        )
        IMPLEMENTED AS DATAFUSION SQL (
            X * 3
        )
    )
SELECT
    DIALECT_MARKER(10) AS V
;

On DuckDB this returns 10 (X * 1). The same UDF definition can return 20 on Trino and 30 on the serving backend via the TRINO / DATAFUSION bodies in the companion examples.

When to use a feature or macro instead

Most custom logic does not need a UDF. If the 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 that by declaring both signatures.

Use a UDF when you need polymorphism across input types, dialect-specific SQL, or a named function callers can use without knowing the implementation. Prefer a macro when a single FeatureQL formula is enough — no SQL bodies required.