CREATE FUNCTION statement

The CREATE FUNCTION statement defines a function.

Supply the function's name using FunctionName and the function's parameters using ParameterList. The function returns a value of the type DataType and is implemented using the single ESQL statement, Statement. This statement can also be a compound statement, for example BEGIN ... END.

Syntax

Notes:
  1. When the NAMESPACE and NAME clauses are used the values are implicitly constant and of type CHARACTER (or CHAR).

For further information on the use of CONSTANT statements see the DECLARE statement.

ESQL functions, unlike ESQL procedures, can have input parameters only. The variables defined within the parameter list of the function definition are read only within the scope of the function. If you try to modify the value of an input parameter variable within a functions compound statement, an exception condition occurs, indicating that this is not possible.

If a parameter is of type REFERENCE, you can issue a MOVE command on the reference variable; this does not modify the value of the variable that it is referencing. However, when the function is complete and processing returns to the caller, the REFERENCE variable refers to the variable that it did before the function was called.

Module functions are local in scope to the current node only. If you want to use the same function in more than one node, define it as a schema function.

When you omit the optional schemaName parameter, the broker SQL parser searches for the named function using the algorithm described in the PATH statement; see the PATH clause.

When you specify the schemaName parameter, the exact function specified is resolved without the search.

Each schema provides a unique function and procedure symbol space.
Note:
  1. If a function invocation is ambiguous, that is, there are two functions with the same name in different schemas, and the reference is not qualified by the optional schemaName, the Eclipse tool set generates a Tasks viewer error that you must correct to deploy the ambiguous code.
  2. Function overloading is not supported
  3. The built-in functions have, in effect, been placed in a predefined schema called SQL. User-defined module level functions of the same name take precedence over these built-in functions
Related concepts
ESQL
Related tasks
Developing ESQL
Related reference
Syntax preference
ESQL statements
DECLARE statement
PATH clause