User-Defined Function

User-Defined Functions (UDFs) in Databend allow you to create custom operations tailored to your specific data processing needs. This page highlights the commands you will use most often and helps you choose the right function type for your use case.

Function Management Commands

Command	Description
CREATE SCALAR FUNCTION	SQL-only scalar function (no external language)
CREATE AGGREGATE FUNCTION	Script UDAF (JavaScript/Python runtimes)
CREATE EMBEDDED FUNCTION	Embedded scalar functions (Python/JavaScript/WASM)
CREATE TABLE FUNCTION	SQL-only table function returning result sets
SHOW USER FUNCTIONS	Lists all user-defined functions
ALTER FUNCTION	Modifies existing functions
DROP FUNCTION	Removes functions

Function Type Comparison

Feature	Scalar SQL	Aggregate (Script)	Tabular SQL	Embedded
Return Type	Single value	Single value	Table/ResultSet	Single value
Language	SQL expressions	JavaScript/Python runtimes	SQL queries	Python/JavaScript/WASM
Performance	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Enterprise Required	No	Python runtime only	No	Python runtime only
Package Support	No	Python: Yes (PACKAGES)	No	Python: Yes (PACKAGES)
Best For	Math calculations String operations Data formatting	Custom aggregations that need scripting logic	Complex queries Multi-row results Data transformations	Advanced algorithms External libraries Control flow logic

Unified Syntax

All local UDF types use consistent $$ syntax:

-- Scalar Function
CREATE FUNCTION func_name(param TYPE) RETURNS TYPE AS $$ expression $$;

-- Tabular Function  
CREATE FUNCTION func_name(param TYPE) RETURNS TABLE(...) AS $$ query $$;

-- Embedded Function
CREATE FUNCTION func_name(param TYPE) RETURNS TYPE LANGUAGE python 
HANDLER = 'handler' AS $$ code $$;