User-Defined Function

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

Function Type Comparison

Feature	Scalar SQL	Tabular SQL	Embedded
Return Type	Single value	Table/ResultSet	Single value
Language	SQL expressions	SQL queries	Python/JavaScript/WASM
Performance	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Enterprise Required	No	No	Python: Yes
Package Support	No	No	Python: Yes
Best For	Math calculations String operations Data formatting	Complex queries Multi-row results Data transformations	Advanced algorithms External libraries Control flow logic

Function Management Commands

Command	Description
CREATE SCALAR FUNCTION	Creates a scalar SQL function using unified syntax
CREATE TABLE FUNCTION	Creates a table function that returns result sets
CREATE EMBEDDED FUNCTION	Creates embedded functions (Python/JavaScript/WASM)
SHOW USER FUNCTIONS	Lists all user-defined functions
ALTER FUNCTION	Modifies existing functions
DROP FUNCTION	Removes functions

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 $$;