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

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

Python runtime requires TiDB Cloud Lake Enterprise. You can install PyPI packages via PACKAGES and import stage files via IMPORTS.

Data type mappings (Python)

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)

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.