CREATE SCALAR FUNCTION



Creates a scalar user-defined function (Scalar UDF). The same CREATE FUNCTION statement supports two implementation styles:

  • SQL expression: Logic expressed purely in SQL; no external runtime is required.
  • Python / JavaScript: Write code and specify the entry point with HANDLER.

If you need to call external systems (HTTP/services), see External Function commands.

Syntax

SQL (expression)

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] <function_name> ( [<parameter_list>] ) RETURNS <return_type> AS $$ <expression> $$ [ DESC='<description>' ]

Python / JavaScript

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] <function_name> ( [<parameter_list>] ) RETURNS <return_type> LANGUAGE <language> [IMPORTS = ('<import_path>', ...)] [PACKAGES = ('<package_name>', ...)] HANDLER = '<handler_name>' AS $$ <function_code> $$ [ DESC='<description>' ]

Parameters

  • <parameter_list>: Optional comma-separated list of parameters with their types (e.g., x INT, y FLOAT)
  • <return_type>: The data type of the function's return value
  • <language>: python, javascript
  • <import_path>: Stage files to import (e.g., @s_udf/your_file.zip)
  • <package_name>: Packages to install from PyPI (Python only; e.g. numpy)
  • <handler_name>: Name of the function in the code to call
  • <function_code>: Implementation code in the specified language

Access control requirements

PrivilegeObject TypeDescription
SUPERGlobal, TableOperates a UDF

To create a user-defined function, the user performing the operation or the current_role must have the SUPER privilege.

SQL

-- Create a function to calculate area of a circle CREATE OR REPLACE FUNCTION area_of_circle(radius FLOAT) RETURNS FLOAT AS $$ pi() * radius * radius $$; -- Create a function to calculate age in years CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$ date_diff('year', birth_date, now()) $$; -- Create a function with multiple parameters CREATE OR REPLACE FUNCTION calculate_bmi(weight_kg FLOAT, height_m FLOAT) RETURNS FLOAT AS $$ weight_kg / (height_m * height_m) $$; -- Use the functions SELECT area_of_circle(5.0) AS circle_area; SELECT calculate_age(to_date('1990-05-15')) AS age; SELECT calculate_bmi(70.0, 1.75) AS bmi;

Python

You can install PyPI packages via PACKAGES and import stage files via IMPORTS.

Data type mappings (Python)

TiDB Cloud Lake TypePython Type
NULLNone
BOOLEANbool
INTint
FLOAT/DOUBLEfloat
DECIMALdecimal.Decimal
VARCHARstr
BINARYbytes
LISTlist
MAPdict
STRUCTobject
JSONdict/list

Examples

CREATE OR REPLACE FUNCTION calculate_age_py(VARCHAR) RETURNS INT LANGUAGE python HANDLER = 'calculate_age' AS $$ from datetime import datetime def calculate_age(birth_date_str): birth_date = datetime.strptime(birth_date_str, '%Y-%m-%d') today = datetime.now() age = today.year - birth_date.year if (today.month, today.day) < (birth_date.month, birth_date.day): age -= 1 return age $$; SELECT calculate_age_py('1990-05-15') AS age;
CREATE OR REPLACE FUNCTION numpy_sqrt(FLOAT) RETURNS FLOAT LANGUAGE python PACKAGES = ('numpy') HANDLER = 'numpy_sqrt' AS $$ import numpy as np def numpy_sqrt(x): return float(np.sqrt(x)) $$; SELECT numpy_sqrt(9.0) AS sqrt_val;

JavaScript

Data type mappings (JavaScript)

TiDB Cloud Lake TypeJavaScript Type
NULLnull
BOOLEANBoolean
INTNumber
FLOAT/DOUBLENumber
DECIMALBigDecimal
VARCHARString
BINARYUint8Array
DATE/TIMESTAMPDate
ARRAYArray
MAPObject
STRUCTObject
JSONObject/Array

Example

CREATE OR REPLACE FUNCTION calculate_age_js(VARCHAR) RETURNS INT LANGUAGE javascript HANDLER = 'calculateAge' AS $$ export function calculateAge(birthDateStr) { const birthDate = new Date(birthDateStr); const today = new Date(); let age = today.getFullYear() - birthDate.getFullYear(); const monthDiff = today.getMonth() - birthDate.getMonth(); if (monthDiff < 0 || (monthDiff === 0 && today.getDate() < birthDate.getDate())) { age--; } return age; } $$;

Worker Management for UDFs

In TiDB Cloud Lake, each UDF has an associated Worker that manages its execution environment in the sandbox. After creating a UDF, you may need to manage its worker for optimal performance and resource utilization.

Creating a Worker for Your UDF

-- Create a worker for your UDF (worker name should match UDF name) CREATE WORKER calculate_age_js WITH size='small', auto_suspend='300', auto_resume='true';

Managing Worker Resources

-- View all workers SHOW WORKERS; -- Adjust worker settings ALTER WORKER calculate_age_js SET size='medium', auto_suspend='600'; -- Add tags for organization ALTER WORKER calculate_age_js SET TAG environment='production', team='analytics', purpose='age-calculation';

Worker Lifecycle

-- Suspend worker when not in use ALTER WORKER calculate_age_js SUSPEND; -- Resume worker when needed ALTER WORKER calculate_age_js RESUME; -- Remove worker when UDF is no longer needed DROP WORKER calculate_age_js;

Environment Variables

For security reasons, environment variables for UDFs are managed separately in the cloud console. After creating a UDF and its worker, configure any required environment variables through the TiDB Cloud Lake interface.

For more information, see Worker Management.

Was this page helpful?