CREATE FUNCTION (Define function)

Function

CREATE FUNCTION defines a function.

Privileges

Owner of a schema

A user can define functions that will be owned by that user.

Format

CREATE function-body
function-body::=FUNCTION [authorization-identifier.] routine-identifier
                   ([SQL-parameter-name data-type
                       [, SQL-parameter-name data-type] ...])
                  RETURNS data-type
                   [LANGUAGE {SQL|JAVA}]
[SQL-compilation-option]
                    {SQL-procedure-statement
                     |external-routine-specification}
SQL-compilation-option::=SUBSTR LENGTH maximum-character-length
external-routine-specification ::= EXTERNAL NAME external-routine-name
                          PARAMETER STYLE parameter-style
parameter-style ::= JAVA

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the function that is being defined.
routine-identifier
Specifies a routine identifier for the function being defined. The same routine identifier can be used in all the owner's routines. However, the owner of the function being defined cannot define routines that are identical in terms of the routine identifier, number of SQL parameters, and positions at which the data types of the SQL parameters occur.
SQL-parameter-name
Specifies the name of a parameter for the function. The same SQL parameter name cannot be specified more than once for the same function.
data-type
Specifies the data type of the paired parameter for the function. The BOOLEAN data type cannot be specified.
If the specified data type is an abstract data type, no authorization identifier is specified, and the default authorization identifier does not have an abstract data type of the same name, and if there is an abstract data type of the same name in the 'MASTER' authorization identifier, that abstract data type is assumed to have been specified.
When JAVA is specified in the LANGUAGE clause, an abstract data type cannot be specified as a data type. See 1.9.2 Type mapping for the specifiable data types.
data-type
Specifies the data type for return values of the function.
The following data types cannot be specified:
  • BOOLEAN
  • An abstract data type when JAVA is specified in the LANGUAGE clause (see 1.9.2 Type mapping for the specifiable data types).
If the data type being specified is the abstract data type and the authorization identifier is omitted, and the default authorization identifier does not have an abstract data type of the same name, the specified abstract data type is assumed, provided that the authorization identifier 'MASTER' has an identically named abstract data type.

Specifies the language used to write the function.

For an external routine, specify JAVA.

SQL
Specifies that the processing part of the function is made up of SQL statements.
JAVA
Specifies that the processing part of the function is specified as an external routine and the function is to be implemented as a Java class method.

Which of these operands is specified determines the other operands that can be specified. Table 3-19 shows the operands that can be specified in conjunction with the LANGUAGE clause.

Table 3-19 Specifiable operands depending on the specification of the LANGUAGE clause of CREATE FUNCTION

Other operandLANGUAGE clause specification
SQLJAVA
SQL parameter data typeYY1
RETURNS clause data typeY2Y1, 2
EXTERNAL NAME[Figure]Y
PARAMETER STYLE[Figure]Y
SQL procedure statementY[Figure]

1 Governed by the type mapping rules; for details of type mapping, see 1.9.2 Type mapping.

2 BOOLEAN cannot be specified.

[SUBSTR LENGTH maximum-character-length]
Specifies the maximum number of bytes for representing a single character.
The value specified for the maximum character length must be in the range from 3 to 6.
This operand is valid only when utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of the UNIX version); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(21) SUBSTR.
Relationships to system definition
When SUBSTR LENGTH is omitted, the value specified in the pd_substr_length operand in the system definition is assumed. For details about the pd_substr_length operand, see the manual HiRDB Version 8 System Definition.
Relationship to client environmental definition
The specification of PDSUBSTREN has no applicability to CREATE FUNCTION. For details about PDSUBSTRLEN, see the manual HiRDB Version 8 UAP Development Guide.
Relationship to the character code type specified in the pdntenv or pdsetup command
This operand is valid only when utf-8 is specified for the character code type.
For all other character code types, only a syntax check is performed and the specification is ignored.

Specifies the SQL procedure statements to be executed by the SQL function (for details of SQL procedure statements, see the General rules section in 7. Routine Control SQL). Only compound statements can be specified in an SQL procedure statement. The last SQL procedure statement executed in an SQL function must be the RETURN statement.

Specifies the external routine, written in Java, that constitutes a Java method. For the external routine name specification conventions, see 1.9.1 Specification of external routines.

Specifies items to be passed as parameters when an external routine is called.

Java function parameters that are defined as an SQL data type are passed as Java method parameters in the Java data type that is associated with the SQL data type.

Return values from the Java method defined in the Java data type are returned as return values from the Java function of the SQL data type associated with the Java data type.

Common rules

  1. An SQL parameter cannot be specified in the target of an assignment statement.
  2. The maximum number of function parameters is 30,000. If anything other than SQL is specified in the LANGUAGE clause, an error may result at the time of execution due to external language specification limitations even though the number of specified function parameters is less than 30,000.
  3. SQL parameters become input SQL parameters.
  4. Only predefined functions can be specified in the SQL procedure statements.
  5. When a function is defined, the HiRDB system defines a special name that identifies that function uniquely. Following are the conventions for determining special names:
    special-name::=F function-name object-ID
    • First byte is the constant 'F'.
    • Function name begins in byte 2 (if the name of the function is longer than 19 bytes, only the first 19 bytes are used).
    • Object ID occupies 10 bytes following the function name (left-justified and padded with trailing zeros).
  6. A function that is defined must not be the same as any system-provided function. Any function that satisfies either of the following sets of conditions cannot be defined:
    (a)
    • The function is associated with two SQL parameters.
    • The data type of the first SQL parameter is an abstract data type.
    • An attribute with the same name as the function name is defined as an attribute in the abstract data type specified by the first SQL parameter.
    • The data type of the second SQL parameter is identical to the data type of an attribute with the same name as the function name.
    (b)
    • The function is associated with one SQL parameter.
    • The data type of the SQL parameter is an abstract data type.
    • An attribute with the same name as the function name is defined as an attribute in the abstract data type specified by the SQL parameter.
  7. If the LANGUAGE clause is omitted or SQL is specified in the LANGUAGE clause, an external routine cannot be specified.
  8. If anything other than SQL is specified in the LANGUAGE clause, an SQL procedure statement cannot be specified.
  9. A function identical to the function being defined in terms of the authorization identifier, routine identifier, and number of arguments cannot be specified in an SQL procedure statement.
  10. A CREATE FUNCTION statement cannot be executed if a function that satisfies all the following conditions is already defined:
    • Has the same authorization identifier as the function being defined
    • Has the same function name as the function being defined
    • Has the same number of parameters as the function being defined
    • Has the same parameter data type as the function being defined
  11. When the SQL compile option is specified in ALTER ROUTINE, the length of the SQL statement that is created by incorporating the SQL compile option in the source CREATE FUNCTION for the procedure to be re-created must not exceed the maximum allowable length for SQL statements.
  12. CREATE FUNCTION cannot be executed from a Java procedure if the execution result invalidates the SQL object being executed.

Notes

  1. The CREATE FUNCTION statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. SQL parameters can have the NULL value.
  3. To execute multiple SQL statements in an SQL procedure statement, use a routine control SQL statement, such as a compound statement.
  4. Defining an SQL function results in creation of an SQL object that specifies access procedures for execution of functions.
  5. When defining a function, if a function having the same number of owners, routine identifiers, and SQL parameters already exists, any SQL object with an effective function, procedure, or trigger that uses the existing function is nullified. Similarly, if a function having the same number of routine identifiers and SQL parameters as the function being defined is in the authorization identifier MASTER, then, of the SQL objects with effective functions, procedures, triggers that use the function in the MASTER authorization identifier, the SQL object with the authorization identifier for the function being defined is nullified.
  6. If an SQL object with an effective function, procedure, or trigger is nullified, any rows containing the function, procedure, and trigger that have been nullified in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  7. Before executing the SQL object with a nullified function, procedure, and trigger, you need to execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER to recreate the SQL object with the function, procedure, or trigger.
  8. Stacks in the operating system may overflow if routine calls are repeated extensively or infinitely in a routine.
  9. SUBSTR LENGTH of the SQL compile option is determined by what is specified when the function is defined or modified; it is not affected by the system definition or client environment variable definition that is in effect when the function is called.