CREATE [PUBLIC] FUNCTION (Define function, define public function)

Function

CREATE FUNCTION defines a function.

CREATE FUNCTION can also be used to define public functions, which are available to all users without them having to qualify the routine identifier with an authorization identifier.

(1) CREATE FUNCTION (Define 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|C}]
[SQL-compilation-option]
                    {SQL-procedure-statement
                     |external-routine-specification}
SQL-compilation-option::=SUBSTR LENGTH maximum-character-length
external-routine-specification ::= EXTERNAL NAME {external-Java-routine-name|external-C-stored-routine-name}
                          PARAMETER STYLE parameter-style
parameter-style ::= {JAVA|RDSQL}

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.
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.
The following data types cannot be specified:
  • Abstract data type if JAVA or C is specified in the LANGUAGE clause
  • BINARY or BLOB type if C is specified in the LANGUAGE clause
For details about the data types that can be specified, see 1.10.1(2) Type mapping or Table 3-21 Correspondence between data type of SQL parameter and data type of parameter passed to C function.
data-type
Specifies the data type for return values of the function.
The following data types cannot be specified:
  • BOOLEAN if a data type other than C is specified in the LANGUAGE clause
  • Abstract data type if JAVA or C is specified in the LANGUAGE clause
  • BINARY or BLOB type if C is specified in the LANGUAGE clause
For details about the data types that can be specified, see 1.10.1(2) Type mapping or Table 3-21 Correspondence between data type of SQL parameter and data type of parameter passed to C function.
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 or C.

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.
C
Specifies that the processing part of the function is specified as an external routine specification and the function is implemented in the C language. If C is specified, the maximum number of SQL parameters is limited to 128.

Whether other operands needs to be specified depends on how this operand was specified. The following table indicates whether other operands need to be specified depending on the LANGUAGE clause specification.

Table 3-19 Whether other operands needs to be specified depending on the LANGUAGE clause specification of CREATE FUNCTION

Other operandLANGUAGE clause specification
SQLJAVAC
EXTERNAL NAMENYY
PARAMETER STYLENJAVARDSQL
SQL procedure statementYNN
Legend:
Y: Specify.
N: Do not specify.
JAVA: Specify JAVA.
RDSQL: Specify RDSQL.
[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 for the UNIX edition); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(20) 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 9 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 9 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.

external-Java-routine-name
Specifies the Java method implemented using the Java language as an external routine. For details about how to specify an external Java routine name, see 1.10.1(1) External Java routine names.
external-C-stored-routine-name
Specifies the Java method implemented using the C language as an external routine. For details about how to specify an external Java routine name, see 1.10.2(1) External C stored routine names.

When specifying C in the LANGUAGE clause, see Table 3-21 Correspondence between data type of SQL parameter and data type of parameter passed to C function.

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

JAVA parameter-style
Parameters for an external Java function, defined in an SQL data type, are passed as parameters to the Java method in a Java data type corresponding to the SQL data type.
The return value of the Java method, defined in a Java data type, is returned as a return value of the external Java function in an SQL data type corresponding to the Java data type.
RDSQL parameter-style
If the number of SQL parameters is n, they are passed to the external C function as a parameter of the C function, as described in the following table.

Table 3-20 Contents passed as parameters of the C function

Parameter no.
(n is number of
SQL parameters)
ContentsDescription of contentsData type
1 to nData part of SQL parameterInput parameter corresponding to data part of SQL parameterData type corresponding to data type of SQL parameter#1
n + 1Return value dataOutput parameter used by the C function that implemented the external C stored routine to set the return value.Data type corresponding to SQL data type set in RETURNS#1
n + 2 to 2n + 1SQL parameter indicator partInput parameter corresponding to indicator part of SQL parameter. If the data is a null value, a negative value is set and passed to the C function.short*
2n + 2Return value indicator partOutput parameter used by the external C stored routine for setting the return value indicator part. 0 is set as the default value.
Set the indicator part in the C function that implemented the external C stored routine, according to the following description.
  • If output value is null value
    Setting: -1
  • If output value is non-null value
    Setting: 0
short*
2n + 3SQLSTATEOutput parameter used by the C function that implemented the external C stored routine to set the SQLSTATE value. The area length is 6 bytes. The SQLSTATE value is set in bytes 1 to 5. Set the SQLSTATE value in the C function that implemented the external C stored routine, according to the following description.
  • If the C function terminates normally
    Setting: '00000'
    SQL execution result: Terminate normally
  • If the C function terminates abnormally
    Setting: Format of value '38XYY' where
    X and Y are values in the following ranges:
    X: I to Z
    Y: 0 to 9 or A to Z
    Examples: '38I01', '38ZCD'
    SQL execution result: SQL error occurs.
  • If the C function ends in an undetermined state
    Setting: Value other than '00000', and not having the format '38XYY'
    SQL execution result: SQL error occurs.
char*
2n + 4Routine nameInput parameter specifying the routine namestruct{
short variable-name-1;
char variable-name-2[30];
}*#2
2n + 5ID nameInput parameter specifying the ID name used to identify the functionstruct{
short variable-name-1;
char variable-name-2[30];
}*#2
2n + 6Message textOutput parameter for setting the detailed reason for an error if an error occurs in the C function that implemented an external C stored routine.
If the value of class 38 is set to SQLSTATE when an external C stored routine finishes execution, an error message with embedded message text is output.
The maximum length of the message text that can be set is 80 bytes.
struct{
short variable-name-1;
char variable-name-2[80];
}*#2
#1
For details about the correspondence between the data type of an SQL parameter that can be set when defining an external C function and the data type of a parameter passed to the C function that implements an external C stored routine, see Table 3-21 Correspondence between data type of SQL parameter and data type of parameter passed to C function.
#2
Sets the character string length (in bytes) to variable-name-1 and the character string indicating the contents to variable-name-2.

Table 3-21 Correspondence between data type of SQL parameter and data type of parameter passed to C function

Data type of
SQL parameter
Data type of parameter passed to C functionLength (bytes)Comments
INT[EGER]int*4--
SMALLINTshort*2--
[LARGE] DEC[IMAL][(p[,s])],
[LARGE] NUMERIC[(p[,s])],
char* #2[Figure]p[Figure] 2[Figure] + 11 [Figure] p[Figure] 38, 0 [Figure] s[Figure] p
FLOAT,
DOUBLE PRECISION
double*8--
SMALLFLT,
REAL
float*4--
CHAR[ACTER][(n)]char* #1n + 11 [Figure] n[Figure] 30,000
CHAR[ACTER][(n)] CHARACTER SET [MASTER.]EBCDIK
CHAR[ACTER][(n)] CHARACTER SET [MASTER.]UTF16char* #1n + 22 [Figure] n[Figure] 30,000
VARCHAR(n)char* #1n + 11 [Figure] n[Figure] 32,000
VARCHAR(n) CHARACTER SET [MASTER.]EBCDIK
CHAR[ACTER] VARYING(n)char* #1n + 11 [Figure] n[Figure] 32,000
CHAR[ACTER] VARYING(n) CHARACTER SET [MASTER.]EBCDIK
VARCHAR(n) CHARACTER SET [MASTER.]UTF16char* #1n + 22 [Figure] n[Figure] 32,000
CHAR[ACTER] VARYING(n) CHARACTER SET [MASTER.]UTF16char* #1n + 22 [Figure] n[Figure] 32,000
NCHAR[(n)],
NATIONAL CHAR[ACTER][(n)]
char* #12n + 11 [Figure] n[Figure] 15,000
NVARCHAR(n),
NATIONAL CHAR[ACTER] VARYING(n),
NCHAR VARYING(n)
char* #12n + 11 [Figure] n[Figure] 16,000
MCHAR[(n)],char* #1n + 11 [Figure] n[Figure] 30,000
MVARCHAR(n)char* #1n + 11 [Figure] n[Figure] 32,000
DATEchar*#44--
TIMEchar*#43--
INTERVAL YEAR TO DAYchar* #35--
INTERVAL HOUR TO SECONDchar* #34--
TIMESTAMP[(p)]char* #4nArea size n is determined as follows, according to the value of p.
  • If p = 0, n = 7
  • If p = 2, n = 8
  • If p = 4, n = 9
  • If p = 6, n = 10
BOOLEANint* #540: False
1: True
Indicator variableshort*2--
Legend:
--: No comment
Note
If the system does not code int type data into a 4-byte binary format, the int data type passed to the C function as a parameter will be converted to a data type that indicates 4-byte binary format data.

#1
The rules for conversion between the SQL data type whose UTF-16 character set specification does not specify (CHAR(n), VARCHAR(n), NCHAR(n), NVARCHAR(n), MCHAR(n), MVARCHAR(n)) and the C language data type (char[n + 1], char[n + 1], char[2n + 1], char[2n + 1], char[n + 1], char[n + 1]) are shown below.
Conversion from an SQL data type to a C language data type (input parameter and input/output parameter for C stored procedure, and input parameter for C stored function):
  • Conversion from CHAR(n) to char[n + 1]
  • Conversion from VARCHAR(n) to char[n + 1]
  • Conversion from NCHAR(n) to char[2n + 1]
  • Conversion from NVARCHAR(n) to char[2n + 1]
  • Conversion from MCHAR(n) to char[n + 1]
  • Conversion from MVARCHAR(n) to char[n + 1]
A null character is added to the end of the character string.
Conversion from a C language data type to an SQL data type (output parameter and input/output parameter for C stored procedure, and return value for C stored function):
  • Conversion from char[n + 1] to CHAR(n)
  • Conversion from char[n + 1] to VARCHAR(n)
  • Conversion from char[2n + 1] to NCHAR(n)
  • Conversion from char[2n + 1] to NVARCHAR(n)
  • Conversion from char[n + 1] to MCHAR(n)
  • Conversion from char[n + 1] to MVARCHAR(n)
The length of the character string HiRDB constructs from the C language character string that it receives is the length from the start to the character before the null character. If the SQL data type is CHAR(n), NCHAR(n), or MCHAR(n) and the length of the character string constructed by HiRDB is not the same as the defined length of the SQL data type, it is filled with spaces to reach the defined length. If no null character is detected within n + 1 sequence elements, an abnormal termination occurs.
The rules for conversion between the SQL data type whose UTF-16 character set specification does specified (CHAR(n), VARCHAR(n)) and the C language data type (char[n + 2], char[n + 2]) are shown below.
Conversion from an SQL data type to a C language data type (input parameter and input/output parameter for C stored procedure, and input parameter for C stored function):
  • Conversion from CHAR(n) to char[n + 2]
  • Conversion from VARCHAR(n) to char[n + 2]
A two-byte null character is added to the end of the character string.
Conversion from a C language data type to an SQL data type (output parameter and input/output parameter for C stored procedure, and return value for C stored function):
  • Conversion from char[n + 2] to CHAR(n)
  • Conversion from char[n + 2] to VARCHAR(n)
The length of the character string HiRDB constructs from the C language character string it receives is the length from the start to character before the null character. If the SQL data type is CHAR(n) and the length of the character string constructed by HiRDB is not the same as the defined length of the SQL data type, it is filled with spaces to reach the defined length. If no two-byte null character is detected within n + 2 sequence elements, an abnormal termination occurs.
#2
DECIMAL type stores data in packed decimal format. For details about DECIMAL type, see 1.2 Data types.
Examples of setting the DECIMAL type value in the C language are shown below.
  • For 123.4567 (odd digit)
    unsigned char ex1[4]={0x12,0x34,0x56,0x7c};
  • For -123.456 (even digit)
    unsigned char ex2[4]={0x01,0x23,0x45,0x6d};
  • For 0 (odd digit)
    unsigned char ex3[1]={0x0c};
#3
INTERVAL YEAR TO DAY and INTERVAL HOUR TO SECOND types store data in packed decimal format. For details about these data types, see 1.2 Data types.
#4
DATE, TIME, and TIMESTAMP types store data in unsigned packed decimal format. For details about these data types, see 1.2 Data types.
#5
BOOLEAN type can only be used as a return value of a C stored function.

Common rules

  1. An SQL parameter cannot be specified in the target of an assignment statement.
  2. The number of function parameters must not exceed 30,000 (128 if C is specified in the LANGUAGE clause). 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. If the name of a function call specified in the function body is qualified with authorization-identifier, you cannot specify a function that has the same authorization identifier, routine name, and number of arguments as the function that you are currently defining.
    If the name of a function call specified in the function body is not qualified with authorization-identifier, you cannot specify a function that has the same routine name and number of arguments as the function that you are currently defining.
  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 routine identifier (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.#
    #
    If the character set specified for a fixed-length character data type and a variable-length character data type are different, they will be treated as different data types.
  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. If the SQL object being executed is invalidated, CREATE FUNCTION cannot be executed from within an external Java procedure.

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. The following shows the invalidation conditions for the SQL object associated with the function definition:
    • If an SQL object for which functions, procedures, or triggers are in effect exists when the function is defined, and if a function with the same number of owners, routine identifiers, and SQL parameters already exist, the SQL object becomes invalid.
    • If there is a function that specifies MASTER as its authorization identifer that has the same number of routine identifiers and SQL parameters as the function being defined, any valid SQL object that belongs to the authorization identifier of the function being defined becomes invalid if it is in a function, procedure, or trigger that uses that MASTER authorization identifier function. In addition, if a valid SQL object is in a public function or a public procedure that has the same authorization identifier as that of the function being defined, the SQL object in that public function or public procedure becomes invalid.
    • If there is a public function that has the same number of routine identifiers and SQL parameters as the function being defined, any valid SQL object that belongs to the authorization identifier of the function being defined becomes invalid if it is in a function, procedure, or trigger that uses that public function. In addition, if a valid SQL object is in a public function or a public procedure that has the same authorization identifier as that of the function being defined, the SQL object in that public function or public procedure becomes invalid.
    For notes about defining or deleting a stored function, see HiRDB Version 9 UAP Development Guide.
  6. Of the invalidated functions indicated in note 5, if a function satisfying either of the following conditions is used in the view definition, the function definition results in an error:
    • Abstract data type is used in the data type of an argument.
    • Abstract data type is used in the data type of a return value.
  7. 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.
  8. Before executing the SQL object with a nullified function, procedure, and trigger, you need to execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER to re-create the SQL object with the function, procedure, or trigger.
  9. Before using a view table that references an invalid function associated with the function definition, ALTER ROUTINE must be executed and the SQL object of the function recreated.
  10. Stacks in the operating system may overflow if routine calls are repeated extensively or infinitely in a routine.
  11. 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.

(2) CREATE PUBLIC FUNCTION (Define public function)

Privileges

Users who own a schema
These users can define their own public functions.

Format

CREATE PUBLIC function-body

Operands

Specifies the function being defined as a public function.

When a function is created as a public function, multiple users can reference the function simply by specifying a routine identifier without each user having to define a function with the same contents.

For descriptions of operands other than [authorization-identifier.]routine-identifier, see (1) CREATE FUNCTION (Define function) under CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.

[authorization-identifier.]routine-identifier
authorization-identifier
The authorization identifier cannot be specified because this is a public function.
routine-identifier
Specifies the routine identifier of the public function being defined. This same routine identifier can be used in the public function.

Common rules

  1. You cannot specify the authorization identifier of [authorization-identifier.]routine-identifier] in the function body.
  2. If the name of a function call specified in the function body is qualified with PUBLIC, you cannot specify a public function that has the same routine identifier and number of arguments as the public function that you are currently defining.
    If the name of a function call specified in the function body is not qualified with authorization-identifier, you cannot specify a function that has the same routine identifier and number of arguments as the public function that you are currently defining.
  3. A CREATE PUBLIC FUNCTION statement cannot be executed if a function that satisfies all of the following conditions is already defined:
    • Has the same routine identifier (function name) as the public function being defined.
    • Has the same number of parameters as the public function being defined.
    • Has the same parameter data types as the public function being defined.#
    #
    If the character set specified for a fixed-length character data type and for a variable-length character data type are different, they will be treated as different data types.
  4. If SQL compile options are specified in ALTER ROUTINE, the length of the SQL statement that is created as a result of incorporating the SQL compile option in the CREATE PUBLIC FUNCTION specification for the procedure being recreated must not exceed the maximum allowable length for SQL statements.
  5. If the SQL object being executed is invalidated, CREATE PUBLIC FUNCTION cannot be executed from within an external Java procedure.
  6. For details about other rules, see (1) CREATE FUNCTION (Define function) under CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.

Notes

  1. The CREATE PUBLIC FUNCTION statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. The following shows the invalidation conditions for an SQL object associated with the public function definition:
    • If an SQL object for which functions, procedures, or triggers are in effect already exists when the public function is defined and a public function with the same number of routine identifiers and SQL parameters already exist, the SQL object becomes invalid.
    • If an SQL object for which functions, procedures, or triggers that specify MASTER for the authorization identifier are in effect already exists when the public function is defined and a function with the same number of routine identifiers and SQL parameters, and for which MASTER is specified as the authorization identifier, already exist, the SQL object becomes invalid.
    For notes about defining or deleting an invalidated function, see HiRDB Version 9 UAP Development Guide.
  3. Among the invalidated functions indicated in note 2, if a function satisfying either of the following conditions is used in the view definition, the public function definition results in an error:
    • Abstract data type is used in the data type of an argument.
    • Abstract data type is used in the data type of a return value.
  4. To use a view table that uses an invalid function, you must execute ALTER ROUTINE and re-create the SQL object of the function.
  5. PUBLIC is set in the column that stores the owner of the dictionary table (such as ROUTINE_SCHEMA in the SQL_ROUTINES table). Also, the authorization identifier that defines the public function is stored in the ROUTINE_CREATOR column of the SQL_ROUTINES table.
  6. Use DROP PUBLIC FUNCTION to delete public functions.
  7. For other notes, see (1) CREATE FUNCTION (Define function) under CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.