You are viewing an older version of this section. View current production version.
CREATE FUNCTION (UDF)
Creates a user-defined scalar-valued function (UDF). A UDF is a callable routine that accepts input parameters, executes programmatic logic, and returns a value (or values).
Syntax
[USING database_name] 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_name
The name of the function. Function names must be unique within a database, and cannot be duplicates of names for other UDFs, tables, views, user-defined table-valued functions (TVFs), user-defined aggregate functions (UDAFs), stored procedures (SPs), or built-in functions such as CONCAT()
, ABS()
, COUNT()
, and so on.
You can also specify database_name
and function_name
together by replacing function_name
with database_name.function_name
instead of specifying the database in USING database_name
. For example, you can write the following:
CREATE FUNCTION db.some_func(a int)
...
Function names are case-insensitive. This is different than the behavior for table names.
parameter_list
Input parameters are optional. Any number of input parameters can be specified, and each must be delimited by a comma (,
). Each input parameter must be given a unique identifier name within the scope of the function. A parameter’s name cannot exceed 64 characters in length.
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. Each valid type is described in the Data Types topic. The following example demonstrates how to declare more than one input parameter, using both scalar and non-scalar data types:
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. Consider the following example:
CREATE FUNCTION default_example(a INT DEFAULT 5)
...
Input parameters can also specify data type modifiers, namely NOT NULL
, NULL
, and COLLATE
. Consider the following example:
CREATE FUNCTION modifier_example(a TEXT NOT NULL COLLATE utf8_bin)
...
data_type
Any scalar-valued or non-scalar valued data type as described in the Data Types topic.
DECLARE variable_list
A list of zero or more variable declaration statements. Variable names must be unique within the scope of the function, and cannot conflict with existing names for other identifiers. Variables may be set with an initial value. The DECLARE
clause is only required once before declaring variables, but each variable declaration can be preceded by its own DECLARE
statement. So, the following two examples are equivalent:
CREATE FUNCTION single_parameter_example(a INT) AS
DECLARE
num INT = 0;
str VARCHAR(255);
...
CREATE FUNCTION multi_declare_example(a INT) AS
DECLARE num INT = 0;
DECLARE str VARCHAR(255);
...
function_body
The function body of a UDF may contain any of the following statements:
- Control Flow 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. Also, the UDF’s definer must have EXECUTE
permissions on any UDFs in the function’s body.
Remarks
MemSQL 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.
MySQL Client Delimiters
When creating a UDF using a MySQL-compatible client connected to MemSQL, you must change the client delimiter to ensure that the function definition is correctly passed to the server. By default, all MySQL clients use a semicolon as a statement delimiter. A problem arises when creating UDFs because they use semicolons as statement delimiters within the function body. Therefore, you must change the delimiter setting before creating your function, and then set it back to a semicolon after the alternate delimiter is no longer needed.
To change the MySQL delimiter, execute the following command before creating a new function:
DELIMITER //
When you’ve finished defining UDFs, make sure to change the MySQL delimiter back to a semicolon:
DELIMITER ;
A complete example is shown below, which can be executed directly in a MySQL-compatible client connected to MemSQL:
DELIMITER //
CREATE FUNCTION plus_one(a INT) RETURNS INT AS
DECLARE
b INT = a;
BEGIN
b += 1;
RETURN b;
END //
DELIMITER ;
Each of the examples in this topic and all MemSQL Procedural SQL (MPSQL) topics include this MySQL delimiter consideration.
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.
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) AS
DECLARE
result VARCHAR(255) = "";
i INT;
previousChar CHAR;
nextChar CHAR;
str VARCHAR(255) = input;
BEGIN
str = LCASE(TRIM(str));
IF LENGTH(str) = 0 THEN
RETURN str;
END IF;
previousChar = SUBSTR(str, 1, 1);
result = CONCAT(result, previousChar);
i = 2;
WHILE i <= LENGTH(str) LOOP
nextChar = SUBSTR(str, i, 1);
IF NOT(previousChar = ' ' AND nextChar = ' ') THEN
result = CONCAT(result, SUBSTR(str, i, 1));
END IF;
previousChar = nextChar;
i += 1;
END LOOP;
RETURN result;
END //
DELIMITER ;
memsql> SELECT normalize_string(" THIS is a test ");
+----------------------------------------+
| normalize_string(" THIS is a test ") |
+----------------------------------------+
| this is a test |
+----------------------------------------+
1 row in set (0.00 sec)
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 AS
BEGIN
IF n <= 1 THEN
RETURN FALSE;
END IF;
FOR i IN 2 .. (n-1) LOOP
EXIT WHEN i * i > n;
IF n % i != 0 THEN
CONTINUE;
END IF;
RETURN FALSE;
END LOOP;
RETURN TRUE;
END //
DELIMITER ;
memsql> SELECT is_prime(101);
+---------------+
| is_prime(101) |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
Convert Number Strings Containing ‘K’ or ‘M’ to Integer
DELIMITER //
CREATE FUNCTION strip_to_int(s VARCHAR(32)) RETURNS INT AS
DECLARE
m CHAR(1) = SUBSTR(s, LENGTH(s), 1); -- K or M
raw_v DECIMAL(18,2) = SUBSTR(s, 1, LENGTH(s) - 1);
BEGIN
IF m = "M" THEN
RETURN raw_v * 1000000;
ELSE
RETURN raw_v * 1000;
END IF;
END //
DELIMITER ;
memsql> select strip_to_int('4.2K');
+----------------------+
| strip_to_int('4.2K') |
+----------------------+
| 4200 |
+----------------------+
1 row in set (0.00 sec)
memsql> select strip_to_int('3M');
+--------------------+
| strip_to_int('3M') |
+--------------------+
| 3000000 |
+--------------------+
1 row in set (0.00 sec)
Accepts JSON parameter and parses JSON values
DELIMITER //
CREATE FUNCTION parses_json(j JSON) RETURNS JSON AS
DECLARE
-- These declared values will be NULL if their JSON key does not exist
value INT = j::value;
should_negate INT = j::should_negate;
BEGIN
IF (value IS NULL) OR (should_negate IS NULL) THEN
RETURN '{"result": "JSON input did not have an expected key"}';
ELSIF should_negate THEN
RETURN JSON_SET_DOUBLE('{}', "result", value * -1);
ELSE
RETURN JSON_SET_DOUBLE('{}', "result", value);
END IF;
END //
DELIMITER ;
memsql> select parses_json('{"value": 10, "should_negate": 0}');
+--------------------------------------------------+
| parses_json('{"value": 10, "should_negate": 0}') |
+--------------------------------------------------+
| {"result":10} |
+--------------------------------------------------+
1 row in set (0.00 sec)
memsql> select parses_json('{"value": 15, "should_negate": 1}');
+--------------------------------------------------+
| parses_json('{"value": 15, "should_negate": 1}') |
+--------------------------------------------------+
| {"result":-15} |
+--------------------------------------------------+
1 row in set (0.01 sec)
memsql> select parses_json('{"something_else": 0, "should_negate": 0}');
+----------------------------------------------------------+
| parses_json('{"something_else": 0, "should_negate": 0}') |
+----------------------------------------------------------+
| {"result":"JSON input did not have an expected key"} |
+----------------------------------------------------------+
1 row in set (0.00 sec)
Related Topics