# CREATE FUNCTION (TVF)

The `CREATE FUNCTION` (TVF) command creates a user-defined table-valued function (TVF). A TVF is a callable routine that accepts input parameters, runs a single `SELECT` statement in the function body, and returns a single table-type value (similar to a view).

To create Wasm-based TVFs, refer to [Create Wasm TVFs](https://docs.singlestore.com/db/v9.1/reference/code-engine-powered-by-wasm/create-wasm-tvfs.md).

## 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:

```sql

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/database-object-case-sensitivity.md) 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:

```sql

CREATE FUNCTION single_param_example(a INT) ...

```

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

```sql

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-tvf.md). 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:

```sql

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:

```sql

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding.md).

**data\_type**

Any scalar-valued data type as described in [the Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) 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:

```sql
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 runs in the following way:

```sql
SELECT * FROM inventory_with_price(1.9900);

```

Once the `inventory_with_price` function has been created, it runs 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 runs 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`:

```sql
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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.

Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## Executing TVFs via the Command Line

Instead of copying/pasting TVFs, you can define a TVF in a `.sql` file and run it. The following example shows how to run TVFs in a `.sql` file:

```shell
% cat userFile.sql

```

```output

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

```shell
% singlestore < userFile.sql
```

> **❗ Important**: The command `singlestore < userFile.sql` runs all the commands in the `.sql` file. The `singlestore-client` package is required to run this command.

## Use CTEs in Table-Valued Functions

Common Table Expressions (CTEs) can be used inside TVFs as part of the single SELECT statement returned by the function.

Example:

```sql
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP
);

INSERT INTO users (user_id, username, email, created_at) VALUES
(1, 'john_doe', 'john@example.com', NOW()),
(2, 'jane_doe', 'jane@example.com', NOW());

CREATE FUNCTION cte_get_user_by_id(ins_user_id INT)
RETURNS TABLE AS RETURN WITH myCTE AS
(SELECT username, user_id FROM users)
SELECT username FROM myCTE
WHERE user_id = ins_user_id;

SELECT * FROM cte_get_user_by_id(1);
```

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

```sql
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 runs without a parameter, the result will produce 10 rows as the default value for `a` is 10, as specified in the `CREATE FUNCTION` statement.

```sql
SELECT * FROM TABLE(SERIES_INT1());

```

```output

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

```sql
SELECT * FROM TABLE(SERIES_INT1(5));

```

```output

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

```sql
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.

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

```

```output

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

```sql
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.

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

```

```output

+---------------------+
| 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)
```

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

```

```output

+---------------------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/mysql-client-delimiters.md) topic for details on MySQL client delimiters.

## Security and Permissions

TVFs in SingleStore use the [Definer Security Model](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security/#section-idm4509084128472032632596517123.md). The TVF’s definer must have the permissions to run 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:

## SELECT Statement Limitations

TVFs may only contain a single `SELECT` statement; any other DML or DDL statement in the TVF function body is invalid. Multiple `SELECT` statements are currently not supported.

## Unique Column Names

Each column in a TVF’s logical result set must have a unique name. Duplicate column names are invalid, and will result in an error.

## Scalar Input Parameters

TVF input parameters may only be scalar value types, such as `INT`, `VARCHAR`, and so on. UDF-specific data types such as `ARRAY` or `RECORD` cannot be used as input parameter types.

## Self-Calling (Recursive Calls)

While a TVF can reference other TVFs in the function body, a TVF may not call itself, directly or indirectly.

## Database Scope

Unlike UDFs, TVFs may only runs within the scope of the database in which they are created. Cross-database TVF execution is not supported. Additionally, referenced TVFs in a TVF function body cannot run TVFs in another database. For example, the following TVF is invalid:

```sql

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

```

## Related Topics

* [DROP FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/drop-function.md)
* [SHOW FUNCTIONS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/show-functions.md)
* [SHOW CREATE FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/show-create-function.md)

***

Modified at: June 12, 2026

Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-tvf/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-tvf/)

(An index of the documentation is available at /llms.txt)