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

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.

Identifies the function name. The function name can also include the catalog name and schema:

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.

You can create a function that has no parameters. For example:

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.

Identifies the data type of for the output of the function.

data_type must be a valid solidDB^® data type: see data_type.

Defines whether the function issues any SQL statements and, if so, what type.

Indicates that SQL statements that do not read or modify SQL data can be executed by the function.

Indicates that only read-only SQL statements can be executed by the function.

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.

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.

The function body must be a valid solidDB^® SQL procedure statement. See CREATE PROCEDURE for more details.

For valid expressions, see expression.

▪The returned value cannot be a complex expression; it can only be a variable or a literal.

▪RETURNS NULL ON NULL INPUT is not supported. If any actual parameter is NULL, a NULL function value is forced automatically.

CREATE FUNCTION hello (s VARCHAR(20))
RETURNS VARCHAR(50)
BEGIN
RETURN ’Hello’;
END

Example 2: Creating and using a function to return week numbers

CREATE FUNCTION week_number (s TIMESTAMP)
RETURNS INTEGER
BEGIN
   DECLARE week INTEGER;
   week := floor(( dayofyear(s) - dayofweek(s) + 7)/7);
   RETURN week;
END

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;

INSERT INTO report VALUES (?, week_number(now()), week_number(?));