Control / script · All operations

Register a user-defined function

A variant of Run a script

Define a scalar, aggregate or window function in code so that SQL or expressions can call it by name.

Status

In arcform

Not built yet

arcform does not do this yet; it would live in how a Protocol runs. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
Apache SeaTunnel (2)
  • Calcite UDFread 2026-09-27

    Next docs, not in 2.3.13: user-defined scalar functions for the Calcite transform

  • SQL UDFread 2026-09-27

    Extend the SQL transform's function library with user-defined Java functions

Ibis (5)
  • AggUDFread 2026-09-27

    User-defined aggregate function, with an optional filter

  • AnalyticVectorizedUDFread 2026-09-27

    Legacy pandas UDF evaluated over a window, returning a column

  • ElementWiseVectorizedUDFread 2026-09-27

    Legacy pandas UDF applied element-wise to whole columns

  • ReductionVectorizedUDFread 2026-09-27

    Legacy pandas UDF that reduces columns to one value

  • ScalarUDFread 2026-09-27

    User-defined scalar function: builtin, Python, pandas or PyArrow

SQLMesh (1)
  • Python macro functionsread 2026-09-27

    User Python functions with the @macro decorator that return SQL strings or SQLGlot expressions

Background

In a relational database, a user-defined function extends the database server with a function that can be evaluated in query statements, usually SQL.[1] In DuckDB, create_function registers a Python function as a user-defined function so that it can be used in SQL queries.[2]

When to use it

Once created, a user-defined function can be invoked in SQL statements where most other built-in functions are allowed, including SELECT statements over data stored in tables.[1] Register a function when the same calculation is wanted in many queries, so that it has one definition and one name. (Meridian's view)

The SQL standard defines a determinism property for a user-defined function, which influences the query optimiser when a SQL statement is compiled.[1] DuckDB assumes by default that a Python user-defined function produces the same output for the same input, and a function that uses randomness must be registered with side_effects set to True.[2] In DuckDB, a Python user-defined function returns NULL for a NULL input by default, unless null_handling is set to special.[2] In DuckDB, an exception thrown in a Python user-defined function fails the query by default, and setting exception_handling to return_null returns NULL instead.[2] Tell the engine the truth about a function's purity and its NULLs, because the defaults describe a typical function, not this one. (Meridian's view)

The method

In SQL, user-defined functions are declared using the CREATE FUNCTION statement.[1] The SQL standard gives each user-defined function properties including its language, parameter style, a specific name unique within the database, its determinism and its SQL-data access.[1] A function's SQL-data access property tells the database whether the function contains no SQL statements, contains SQL that reads no tables or views, reads data, or modifies data.[1] Some database management systems allow user-defined functions in languages other than SQL: Microsoft SQL Server allows .NET languages including C#, and DB2 and Oracle support functions written in C or Java.[1]

DuckDB's create_function takes the function's name, the Python function, its parameter types and its return type.[2] When parameter types are left out, DuckDB takes them from the Python function's annotations, but the return type must be annotated or given as return_type.[2]

Apache SeaTunnel extends the function library of its SQL transform with user-defined functions packaged as a jar and copied to ${SEATUNNEL_HOME}/lib.[3]

Compared with the alternatives

A stored procedure groups a set of SQL commands and is not an expression, so it cannot be used the way a user-defined function is.[1] A function must return a value, which is not mandatory for a stored procedure, and a function can be used in a SELECT statement provided it does no data manipulation.[4]

Sources

  1. [1]User-defined function - Wikipediasecondary source
  2. [2]Python Function API – DuckDBdocumentation
  3. [3]SQL UDF | Apache SeaTunneldocumentation
  4. [4]Stored procedure - Wikipediasecondary source