CREATE FUNCTION (UDF)
On this page
Creates a user-defined scalar-valued function (UDF).
Syntax
CREATE [OR REPLACE] FUNCTION function_name ( [parameter_list] )
RETURNS { data_type [data_type_modifier] } AS
[DECLARE variable_list] [ ...n ]
BEGIN
function_body
END ;
parameter_list:
variable_name data_type [data_type_modifier [ ...n ] ] [, ...]
data_type_modifier:
DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name
variable_list:
variable_name data_type [= initial_value] ; [... ;]
Arguments
OR REPLACE
If specified, replaces a UDF if one already exists with the same name.
function_
The name of the function.CONCAT()
, ABS()
, COUNT()
, and so on.
You can also specify database_
and function_
together by replacing function_
with database_
instead of defaulting to the current context database.
CREATE FUNCTION db.some_func(a int)...
Function names are not case-sensitive.
parameter_
Input parameters are optional.,
).
The following example shows how to declare a single input parameter:
CREATE FUNCTION single_param_example(a INT)...
UDFs allow both scalar data types and non-scalar data types (ARRAY
and RECORD
) as input parameters.
CREATE FUNCTION multi_param_example(a INT, b ARRAY(BIGINT NOT NULL), c DECIMAL(19,4))...
Default values can be specified for input parameters by using the DEFAULT
constraint.
CREATE FUNCTION default_example(a INT DEFAULT 5)...
Input parameters can also specify data type modifiers, namely NOT NULL
, NULL
, and COLLATE
.
CREATE FUNCTION modifier_example(a TEXT NOT NULL COLLATE utf8_bin)...
You can use parameters with any supported character sets using the following syntax.
CREATE FUNCTION function_name(id INT, product VARCHAR(20) COLLATE utf8_general_ci);
…
data_
Any scalar-valued or non-scalar valued data type as described in the Data Types topic.
DECLARE variable_
A list of zero or more variable declaration statements.DECLARE
clause is only required once before declaring variables, but each variable declaration can be preceded by its own DECLARE
statement.
CREATE FUNCTION single_parameter_example(a INT) ASDECLAREnum INT = 0;str VARCHAR(255);...
CREATE FUNCTION multi_declare_example(a INT) ASDECLARE num INT = 0;DECLARE str VARCHAR(255);...
You can use variables with any supported character sets using the following syntax.
CREATE FUNCTION single_parameter_example(a INT) AS
DECLARE
num INT = 0;
str VARCHAR(255) COLLATE utf8_general_ci;
...
function_
The function body of a UDF may contain any of the following statements:
-
Variable assignments
-
Calls to other UDFs when assigning them to a variable or in a
RETURN
statement.
Security and Permissions
The invoker of a UDF must have EXECUTE
permissions on the UDF.EXECUTE
permissions on any UDFs used in the function’s body.
Remarks
SingleStore user-defined scalar-valued functions (UDFs) provide a mechanism to create custom programmatic logic that can be called in SQL queries, stored procedures, or in other UDFs.
You may reference a system-defined global or session variable inside a user-defined function by using its identifier in a place where a constant value may appear.
This command causes implicit commits.
MySQL Client Delimiters
When creating a UDF using a MySQL-compatible client connected to SingleStore, you must change the client delimiter to ensure that the function definition is correctly passed to the server and then set it back to a semicolon after the alternate delimiter is no longer needed.
Limitations
UDFs are limited in the following ways:
Calling Limitations
A UDF cannot call a stored procedure (SP) or a user-defined aggregate function (UDAF).
Function Overloading
A UDF definition cannot be overloaded by changing the function signature, such as adding or removing input parameters or changing the return type.
SQL and Database Object Manipulation
UDFs cannot execute SQL statements, and therefore cannot read or modify any persistent database objects.
Refer to the Permission Matrix for the required permission.
Examples
Normalize String
The following example normalizes a string by making all letters lowercase, removing any leading and trailing whitespace characters, and ensuring one whitespace character between words.
DELIMITER //CREATE FUNCTION normalize_string(input VARCHAR(255)) RETURNS VARCHAR(255) ASDECLAREresult VARCHAR(255) = "";i INT;previousChar CHAR;nextChar CHAR;str VARCHAR(255) = input;BEGINstr = LCASE(TRIM(str));IF LENGTH(str) = 0 THENRETURN str;END IF;previousChar = SUBSTR(str, 1, 1);result = CONCAT(result, previousChar);i = 2;WHILE i <= LENGTH(str) LOOPnextChar = SUBSTR(str, i, 1);IF NOT(previousChar = ' ' AND nextChar = ' ') THENresult = CONCAT(result, SUBSTR(str, i, 1));END IF;previousChar = nextChar;i += 1;END LOOP;RETURN result;END //DELIMITER ;
SELECT normalize_string(" THIS is a test ");
+----------------------------------------+
| normalize_string(" THIS is a test ") |
+----------------------------------------+
| this is a test |
+----------------------------------------+
Is Prime Number
The following example function accepts an integer input parameter and returns 1
if it is a prime number or 0
if it is not a prime number.
DELIMITER //CREATE FUNCTION is_prime(n BIGINT NOT NULL) returns BIGINT ASBEGINIF n <= 1 THENRETURN FALSE;END IF;FOR i IN 2 .. (n-1) LOOPEXIT WHEN i * i > n;IF n % i != 0 THENCONTINUE;END IF;RETURN FALSE;END LOOP;RETURN TRUE;END //DELIMITER ;
SELECT is_prime(101);
+---------------+
| is_prime(101) |
+---------------+
| 1 |
+---------------+
Referencing Variables
The following example shows how to use engine variables inside a user-defined function.
DELIMITER //CREATE OR REPLACE FUNCTION getVer() RETURNS VARCHAR(255) ASDECLAREvar1 VARCHAR(255);var2 VARCHAR(255);res VARCHAR(255);BEGINvar1 = @@memsql_version;var2 = @@version_compile_os;res = CONCAT("Version: ",var1, "; OS: ", var2);RETURN res;END //DELIMITER ;
SELECT getVer() As Info;
+---------------------------+
| Info |
+---------------------------+
| Version: 7.3.1; OS: Linux |
+---------------------------+
Related Topics
Last modified: April 30, 2024