📣

TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →

Get Started

Quick Start

Guides

Connect
Manage Resources
Data Integration
Load Data
Unload Data
Multimodal Data Analytics
Performance Tuning
Security & Reliability
Management

Troubleshooting

Tutorials

Ingest & Stream Data
Migrate Data

Reference

SQL Reference
General Reference

Support Services

User-Defined Function

User-Defined Functions (UDFs) in TiDB Cloud Lake 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.

Tip

Check the built-in functions first. TiDB Cloud Lake ships hundreds of built-in functions covering math, strings, dates, JSON, aggregation, and more. Before writing a UDF, browse the SQL Function Reference to see if one already does what you need. Reach for a UDF when the built-ins can't express your logic.

Function Management Commands

Command	Description
CREATE AGGREGATE FUNCTION	Script UDAF (JavaScript/Python runtimes)
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)	Scalar (Python/JavaScript)	Aggregate (Script)	Tabular SQL
Return Type	Single value	Single value	Single value	Table/ResultSet
Language	SQL expressions	Python/JavaScript	JavaScript/Python runtimes	SQL queries
Performance	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Enterprise Required	No	Python runtime only	Python runtime only	No
Package Support	No	Python: Yes (PACKAGES)	Python: Yes (PACKAGES)	No
Best For	Math calculations String operations Data formatting	Advanced algorithms External libraries Control flow logic	Custom aggregations that need scripting logic	Complex queries Multi-row results Data transformations

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

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

User-Defined Function

Function Management Commands

Function Type Comparison

Unified Syntax

Was this page helpful?