SQL Guide : solidDB® SQL statements : CREATE FUNCTION
  
CREATE FUNCTION
CREATE FUNCTION function_name [(parameter_definition
      [, parameter_definition ...])]
      RETURN[S] data_type
   [SQL_data_access_indication]
   [LANGUAGE SQL]
      BEGIN
            function_body
      END
parameter_definition::= parameter_name
                        data_type[=literal]
SQL_data_access_indication::= CONTAINS SQL | READS SQL DATA | NO SQL
Usage
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 catalog name 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.
parameter_definition:= parameter_name data_type[=literal]
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.
CONTAINS SQL
Indicates that SQL statements that do not read or modify SQL data can be executed by the function.
READS SQL DATA
Indicates that only read-only SQL statements can be executed by the function.
NO SQL
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.
LANGUAGE SQL
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.
For valid expressions, see expression.
Return values
SQL_ERROR
SQL_SUCCESS
Restrictions
The returned value cannot be a complex expression; it can only be a variable or a literal.
COMMIT is not allowed within a function.
RETURNS NULL ON NULL INPUT is not supported. If any actual parameter is NULL, a NULL function value is forced automatically.
Example 1: Creating and using a simple function hello
Creating the function:
CREATE FUNCTION hello (s VARCHAR(20))
RETURNS VARCHAR(50)
BEGIN
   RETURN 'Hello';
END
Using the function:
SELECT hello('world');
HELLO('world')
-------------
Hello
Example 2: Creating and using a function to return week numbers
Creating the function:
CREATE FUNCTION week_number (s TIMESTAMP)
RETURNS INTEGER
BEGIN
   DECLARE week INTEGER;
   week := floor(( dayofyear(s) - dayofweek(s) + 7)/7);
   RETURN week;
END
Using the function:
SELECT quota, week_number(sales_date)AS week, week_number(now())
AS current_week
   FROM sales ORDER BY quota DESC LIMIT 1;
QUOTA WEEK CURRENT_WEEK
­­­­­­­­­­­­­­­­­­-----------------------
467   23             43
SELECT quota FROM sales
 WHERE current_week(sales_date) = 23 ORDER BY quota DESC LIMIT 1;
QUOTA
-----
  467
INSERT INTO report VALUES (?, week_number(now()), week_number(?));
Related reference
See also
solidDB® SQL statements