CREATE FUNCTION (TVF)

Syntax

CREATE [OR REPLACE] FUNCTION function_name ( [parameter_list] )
  RETURNS TABLE AS RETURN function_body ;

parameter_list:
  variable_name data_type [data_type_modifier] [, ...]

data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

Return Type

• return_type: A scalar value or a TABLE value. If a TABLE value, includes a list of <column name> <data_type> values such as TABLE(a TEXT, b INT).

Arguments

OR REPLACE

If specified, replaces a TVF 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 TVFs, tables, views, user-defined functions (UDFs), user-defined aggregate functions (UDAFs), or stored procedures.

You can also specify database_name and function_name together by replacing function_name with database_name.function_name instead of defaulting to the current context database. For example, you can write the following:

CREATE FUNCTION db.some_func(a int)
...

Table Valued Function names are case-sensitive. For details on case-sensitivity, refer to the Database Object Case-Sensitivity topic.

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.

The following example shows how to declare a single input parameter:

CREATE FUNCTION single_param_example(a INT) ...

The following example demonstrates how to declare more than one input parameter:

CREATE FUNCTION multi_param_example(a INT, b VARCHAR(255), c DECIMAL(19,4)) ...

Only scalar data types are allowed for TVF input parameters, and each valid type is enumerated in the syntax block above. Non-scalar types, such as ARRAY or RECORD, cannot be declared as input parameters for TVFs.

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) ...

You can use parameters with any supported character sets using the following syntax.

CREATE FUNCTION function_name(product_id INT, feedback VARCHAR(20) COLLATE utf8_general_ci);
…

In a similar fashion, you can use variables with any supported character sets.

For more information, see Character Encoding.

data_type

Any scalar-valued data type as described in the Data Types topic. Non-scalar valued types are not allowed in TVFs, including ARRAY and RECORD types.

function_body

The function body of a TVF may contain any valid SELECT statement.

Remarks

Table-valued functions (TVFs) in SingleStore provide a mechanism to return a result set from a single user-defined SELECT statement, much like a view. Unlike views, however, TVFs accept scalar-type input parameters that can be used inside the function body. Consider the following example TVF, which accepts a DECIMAL input type and returns the items in a store’s inventory that match the desired price:

CREATE FUNCTION inventory_with_price(price DECIMAL(19,4))
  RETURNS TABLE AS RETURN
    SELECT * FROM store_inventory WHERE item_price = price;

This TVF specifies an input parameter named price, which is used to query the store_inventory table and find all items of the specified price. The function is executed in the following way:

SELECT * FROM inventory_with_price(1.9900);

Once the inventory_with_price function has been created, it can be executed with different DECIMAL input values to query the store_inventory table in a dynamic, reusable manner.

TVFs can also reference other TVFs in the function body. When a referenced TVF is executed inside the function body of a TVF, the result set from the referenced TVF can be queried like a table. Consider the following example where tvf_2 references tvf_1:

CREATE FUNCTION tvf_1(a INT)
  RETURNS TABLE AS RETURN
    SELECT * FROM store_inventory
    LIMIT a;

CREATE FUNCTION tvf_2()
  RETURNS TABLE AS RETURN
    SELECT * FROM tvf_1(10)
    GROUP BY item_price
    ORDER BY item_price
    LIMIT 10;

In the example above, a table named store_inventory contains a item_price column. The tvf_1 function is referenced in tvf_2 and its logical result set is queried as a table type.

This command causes implicit commits. Refer to COMMIT for more information.

Refer to the Permission Matrix for the required permission.

% cat userFile.sql

USE db1;
CREATE OR REPLACE FUNCTION tvf_2(num INT)
  RETURNS TABLE AS
  RETURN SELECT * FROM stock
  LIMIT num;
SELECT * FROM  tvf_2();

% singlestore < userFile.sql

Generate a Series of Numbers or Dates

The CREATE FUNCTION(TVF) command can be used to create a series of numbers or dates.

The following example creates a series of integers starting with 1.

DELIMITER //
CREATE FUNCTION SERIES_INT1(a INT DEFAULT 10) RETURNS ARRAY(INT) AS
DECLARE
 num_arr ARRAY(INT) = CREATE_ARRAY(a);
BEGIN
 FOR i IN 1..a LOOP
  num_arr[i-1] = i;
 END LOOP;
RETURN num_arr;
END //
DELIMITER ;

The TABLE function can be used to convert the array returned from the SERIES_INT1()function into rows of sequential integers. If the TABLE function is executed without a parameter, the result will produce 10 rows as the default value for a is 10, as specified in the CREATE FUNCTION statement.

SELECT * FROM TABLE(SERIES_INT1());

+-----------+
| table_col |
+-----------+
|         1 |
|         2 |
|         3 |
|         4 |
|         5 |
|         6 |
|         7 |
|         8 |
|         9 |
|        10 |
+-----------+

SELECT * FROM TABLE(SERIES_INT1(5));

+-----------+
| table_col |
+-----------+
|         1 |
|         2 |
|         3 |
|         4 |
|         5 |
+-----------+

The following function provides a more customized numeric series. This function allows the starting value, incremental value, and number of elements in the series to be set by the user.

DELIMITER //
CREATE FUNCTION SERIES_INT2(start_value INT, increment_value INT, num_elements INT) RETURNS ARRAY(INT) AS
DECLARE
    num_arr ARRAY(INT) = CREATE_ARRAY(num_elements);
    current_value INT := start_value;
BEGIN
    FOR i IN 1..num_elements LOOP
        num_arr[i-1] = current_value;
        current_value := current_value + increment_value;
    END LOOP;
    RETURN num_arr;
END //
DELIMITER ;

The following execution of the function TABLE(SERIES_INT2()) starts the series at 2, increments by 3, and produces 6 elements total.

SELECT * FROM TABLE(SERIES_INT2(2,3,6));

+-----------+
| table_col |
+-----------+
|         2 |
|         5 |
|         8 |
|        11 |
|        14 |
|        17 |
+-----------+

The following function creates a series of dates with starting dates, ending dates, and a pre-set date interval supplied by the user.

DELIMITER //
CREATE OR REPLACE FUNCTION SERIES_DATE1(_start DATETIME, _stop DATETIME, _step VARCHAR(40))
RETURNS ARRAY(DATETIME) AS
DECLARE
  arr ARRAY(DATETIME);
  final_arr ARRAY(DATETIME);
  l_interval VARCHAR(20);
  step INT;
  l_count INT;
  startdt DATETIME;
  enddt DATETIME;
BEGIN
  IF _step REGEXP 'INTERVAL [0-9]+ (SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR)' = 1 THEN
      l_interval = SUBSTRING_INDEX(_step, ' ', -1);
      step = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(_step, ' ', 2), ' ', -1) AS UNSIGNED);
  ELSE
       RAISE user_exception('step parameter should be in the form INTERVAL n SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR..');
  END IF;
  arr = CREATE_ARRAY(1000000);
  l_count = 0;
  startdt = _start;
  enddt = _stop;
  arr[l_count] = startdt;
  WHILE startdt <= enddt LOOP
     IF UPPER(l_interval) = 'SECOND' THEN
        startdt = DATE_ADD(startdt, INTERVAL step SECOND);
     ELSEIF UPPER(l_interval) = 'MINUTE' THEN
        startdt = DATE_ADD(startdt, INTERVAL step MINUTE);
     ELSEIF UPPER(l_interval) = 'HOUR' THEN
        startdt = DATE_ADD(startdt, INTERVAL step HOUR);
     ELSEIF UPPER(l_interval) = 'DAY' THEN
        startdt = DATE_ADD(startdt, INTERVAL step DAY);
     ELSEIF UPPER(l_interval) = 'WEEK' THEN
        startdt = DATE_ADD(startdt, INTERVAL step WEEK);
     ELSEIF UPPER(l_interval) = 'MONTH' THEN
        startdt = DATE_ADD(startdt, INTERVAL step MONTH);
     ELSEIF UPPER(l_interval) = 'YEAR' THEN
        startdt = DATE_ADD(startdt, INTERVAL step YEAR);
    END IF;
    l_count = l_count + 1;
    arr[l_count] = startdt;
  END LOOP;
  -- final array to output
  final_arr = CREATE_ARRAY(l_count+1);
  FOR i IN 0..l_count LOOP
    final_arr[i] = arr[i];
  END LOOP;
  return final_arr;
END //
DELIMITER ;

The TABLE function converts the returned array into rows of sequential dates. The first query has an interval set at 1 day. The second query has an interval set at 1 week.

SELECT * FROM TABLE(SERIES_DATE1('2024-04-29','2024-06-30','INTERVAL 1 DAY'));

+---------------------+
| table_col           |
+---------------------+
| 2024-04-29 00:00:00 |
| 2024-04-30 00:00:00 |
| 2024-05-01 00:00:00 |
| . . . . . . . . . . |
| . . . . . . . . . . |
| 2024-06-29 00:00:00 |
| 2024-06-30 00:00:00 |
| 2024-07-01 00:00:00 |
+---------------------+
64 rows in set (0.03 sec)

SELECT * FROM TABLE(SERIES_DATE1('2024-04-29','2024-06-30','INTERVAL 1 WEEK'));

+---------------------+
| table_col           |
+---------------------+
| 2024-04-29 00:00:00 |
| 2024-05-06 00:00:00 |
| 2024-05-13 00:00:00 |
| 2024-05-20 00:00:00 |
| 2024-05-27 00:00:00 |
| 2024-06-03 00:00:00 |
| 2024-06-10 00:00:00 |
| 2024-06-17 00:00:00 |
| 2024-06-24 00:00:00 |
| 2024-07-01 00:00:00 |
+---------------------+
10 rows in set (0.03 sec)

MySQL Client Delimiters

When creating a TVF 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. See the MySQL Client Delimiters topic for details on MySQL client delimiters.

Security and Permissions

TVFs in SingleStore use the Definer Security Model. The TVF’s definer must have the permissions to execute the SELECT statement that makes up the TVF’s function body. The invoker of a TVF must have EXECUTE permissions on the TVF. Also, the TVF’s definer must have EXECUTE permissions on any TVFs used in the function’s body.

Limitations

TVFs are limited in the following ways:

CREATE FUNCTION cross_database_invalid()
  RETURNS TABLE AS RETURN
    SELECT * FROM otherdb.other_tvf();

Related Topics

• DROP FUNCTION

• SHOW FUNCTIONS

• SHOW CREATE FUNCTION