The CREATE FUNCTION statement creates user-defined stored functions. Stored functions are written in the solidDB® proprietary SQL procedure language.
You can use the stored SQL functions to expand the server functionality by writing functions that behave in the same way as built-in functions.
Stored functions accept literals, variables, variable markers (“?”) or column names as input parameters. Output parameters are not supported.
Parameters
function_name
Identifies the function name. The function name can also include the catalogname and schema:
[catalog_name[.schema].]function_name
The name must be a valid SQL identifier. The function name must be unique within the schema. Although there is no prohibition against it, the user-defined function should not be given the same name as a built-in function.
Identifies the number of input parameters of the function, and specifies the name and data type, and optional default value of each parameter. One entry in the list must be specified for each parameter that the function expects to receive. Up to 90 parameters can be specified.
Example:
CREATE FUNCTION week_number (s TIMESTAMP) ...
You can create a function that has no parameters. For example:
CREATE FUNCTION howdy() RETURNS VARCHAR20 ...
data_type must be a valid solidDB® data type: see data_type.
literal defines the default value of the parameter. The value must be a literal.
Stored functions accept literals, variables, variable markers (“?”) or column names as input parameters. If a column name is used, the column value is retrieved from the current row variable instance (for example in the WHERE clause). Output parameters are not supported.
RETURN[S] data_type
Identifies the data type of for the output of the function.
data_type must be a valid solidDB® data type: see data_type.
SQL_data_access_indication
Defines whether the function issues any SQL statements and, if so, what type.
CONTAINSSQL
Indicates that SQL statements that do not read or modify SQL data can be executed by the function.
READSSQLDATA
Indicates that only read-only SQL statements can be executed by the function.
NOSQL
Indicates that the function cannot execute any SQL statements.
Note The validity of the contents of the function are not checked against the declared data access indication.
LANGUAGESQL
This optional clause indicates that the CREATE FUNCTION statement is being used to register a new function written in solidDB® SQL procedure language. See CREATE FUNCTION (external) for details about how to create external functions written in C programming language.
function_body
The function body must be a valid solidDB® SQL procedure statement. See CREATE PROCEDURE for more details.