UDF()

All functions > CORE > UDF()

Defines a polymorphic user-defined function with typed signatures and dialect SQL expressions.

Signatures

Returns: A UDF function reference

UDF(param_names: ARRAY<VARCHAR>, signatures: ARRAY<ARRAY<TYPE>>, impl_dialects: ARRAY<VARCHAR>, impl_sqls: ARRAY<VARCHAR>) → UDF
sql
ParameterTypeRequiredDescription
param_namesARRAY<VARCHAR>YesOrdered parameter names shared by every SIGNATURE
signaturesARRAY<ARRAY<TYPE>>YesEach entry is parameter types followed by the return type
impl_dialectsARRAY<VARCHAR>YesSQL dialects with an explicit implementation body
impl_sqlsARRAY<VARCHAR>YesScalar SQL expression body for each dialect in impl_dialects

Notes

  • Declares a compile-time scalar UDF with one or more SIGNATURE overloads (same parameter names and arity)
  • Declare IMPLEMENTED AS <dialect> SQL (...) for each backend that should run the UDF (DUCKDB, TRINO, BIGQUERY, DATAFUSION)
  • There is no DEFAULT fallback and no cross-dialect rewrite of user SQL; a missing body raises UE/UDF-NOT-SUPPORTED-DIALECT
  • DEFAULT IMPLEMENTED AS is rejected (UE/UDF-DEFAULT-NOT-ALLOWED); the keyword remains in the grammar only
  • Parameter names in SQL bodies are replaced with call-site argument expression trees
  • Bodies are plain scalar SQL only — no subqueries/tables, and no template helpers in the body
  • Prefer MACRO() when polymorphism or dialect-specific SQL is not required

Examples

Polymorphic UDF with per-dialect SQL bodies (4-udfs.slt)

FeatureQL
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
;
Result
QUARTER_FROM_DATE VARCHARQUARTER_FROM_TIMESTAMP VARCHAR
2025Q32025Q4

Last update at: 2026/08/04 11:25:38