You are viewing an older version of this section. View current production version.
CREATE FUNCTION (TVF)
Creates a user-defined table-valued function (TVF). A TVF is a callable routine that accepts input parameters, executes a single SELECT
statement in the function body, and returns a single table-type value.
Syntax
[USING database_name] 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
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 specifying the database in USING database_name
. For example, you can write the following:
CREATE FUNCTION db.some_func(a int)
...
Table Valued Function names are case-sensitive. This matches the behavior of 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.
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) ...
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 MemSQL 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.
Security and Permissions
TVFs in MemSQL 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.
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 be executed 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 execute TVFs in another database. For example, the following TVF is invalid:
CREATE FUNCTION cross_database_invalid()
RETURNS TABLE AS RETURN
SELECT * FROM otherdb.other_tvf();
Related Topics