CREATE FUNCTION (TVF)
On this page
Creates a user-defined table-valued function (TVF).SELECT
statement in the function body, and returns a single table-type value (similar to a view).
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_
values such astype> TABLE(a TEXT, b INT)
.
Arguments
OR REPLACE
If specified, replaces a TVF if one already exists with the same name.
function_
The name of the function.
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)...
Table Valued Function names are 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) ...
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.ARRAY
or RECORD
, cannot be declared as input parameters for TVFs.
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(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_
Any scalar-valued data type as described in the Data Types topic.ARRAY
and RECORD
types.
function_
The function body of a TVF may contain any valid SELECT
statement.
Remarks
Table-valued functions (TVFs) in SingleStoreDB provide a mechanism to return a result set from a single user-defined SELECT
statement, much like a view.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 RETURNSELECT * FROM store_inventory WHERE item_price = price;
This TVF specifies an input parameter named price
, which is used to query the store_
table and find all items of the specified price.
SELECT * FROM inventory_with_price(1.9900);
Once the inventory_
function has been created, it can be executed with different DECIMAL
input values to query the store_
table in a dynamic, reusable manner.
TVFs can also reference other TVFs in the function body.tvf_
references tvf_
:
CREATE FUNCTION tvf_1(a INT)RETURNS TABLE AS RETURNSELECT * FROM store_inventoryLIMIT a;CREATE FUNCTION tvf_2()RETURNS TABLE AS RETURNSELECT * FROM tvf_1(10)GROUP BY item_priceORDER BY item_priceLIMIT 10;
In the example above, a table named store_
contains a item_
column.tvf_
function is referenced in tvf_
and its logical result set is queried as a table type.
This command causes implicit commits.
Refer to the Permission Matrix for the required permission.
Executing TVFs via the Command Line
Instead of copying/pasting TVFs, you can define a TVF in a .
file and execute it..
file:
% 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
Important
The command singlestore < userFile.
runs all the commands in the .
file.singlestore-client
package is required to run this command.
MySQL Client Delimiters
When creating a TVF using a MySQL-compatible client connected to SingleStoreDB, 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.
Security and Permissions
TVFs in SingleStoreDB use the Definer Security Model.SELECT
statement that makes up the TVF’s function body.EXECUTE
permissions on the TVF.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.SELECT
statements are currently not supported.
Unique Column Names
Each column in a TVF’s logical result set must have a unique name.
Scalar Input Parameters
TVF input parameters may only be scalar value types, such as INT
, VARCHAR
, and so on.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 be executed within the scope of the database in which they are created.
CREATE FUNCTION cross_database_invalid()RETURNS TABLE AS RETURNSELECT * FROM otherdb.other_tvf();
Related Topics
Last modified: January 9, 2023