Updated on 2025-09-18 GMT+08:00

CREATE FUNCTION

Function

Creates a custom function.

Syntax

Syntax for creating a custom function.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE FUNCTION function_name 
    ( [  { argname  argtype } ]  [, ...]  ] )
    [ RETURNS rettype ]
    LANGUAGE lang_name 
    [ { IMMUTABLE  | STABLE  | VOLATILE } ]
    RUNTIME_VERSION = { 'version' }
    HANDLER = 'function_name'
    COMMENT = 'comment'
    [ STRICT ]
    [ PACKAGES = ( 'package_name==[version]' [ , ...] ) ]
    [ IMPORTS = ( 'obs_file_path' ) ]
    [ AS 'definition' ]

Parameter Description

Table 1 CREATE FUNCTION parameters

Parameter

Description

Value Range

function_name

Name of the function to be created (can be schema-qualified).

String, which must conform to the identifier naming conventions.

You are advised to specify a schema when creating a function. Otherwise, the function will be created under the default schema default_db. When calling a custom function, a schema must be specified. Otherwise, the system will call a built-in function.

argname

Name of a function parameter.

String, which must conform to the identifier naming conventions.

argtype

Type of a function parameter.

See Data Type Mapping.

rettype

Data type of the function's return value.

See Data Type Mapping.

LANGUAGE lang_name

Name of the language used to implement the function. Custom functions currently support only Python.

-

IMMUTABLE

Indicates that the function always returns the same result when given the same parameter values.

If the function's input parameter is a constant, the function's value is computed during the optimizer stage. This allows earlier access to the expression value, enabling more accurate cost estimation and generating an optimal execution plan.

STABLE

Indicates that the function cannot modify the database. For the same parameter values, the function's return value remains unchanged within the same table scan, but may vary across different SQL statements.

-

VOLATILE

Indicates that the function's value can change within a single table scan, hence no optimizations are applied.

-

PACKAGES

Indicates the third-party Python packages required by the function runtime environment.

-

RUNTIME_VERSION

Indicates the specific Python version for the function runtime environment. Currently, only the 3.11 series is supported.

-

HANDLER

Indicates the main function entry.

-

IMPORTS

Path to the compressed package on OBS that the function depends on at runtime.

Only one path can be introduced in the IMPORTS clause, that is, one compressed package.

STRICT

Used to specify that the function always returns NULL if any of its parameters are NULL. Declaring this parameter means the function will not execute with NULL-valued parameters. Instead, it automatically returns a NULL result.

-

definition

Implementation of the function body.

The default value is null, and the length cannot exceed 1,000 characters.

comment

Description of the function.

-