CREATE FUNCTION
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE FUNCTION statement creates a user-defined function (UDF).
This statement has the following format:
[EXEC SQL] [CREATE | CREATE OR REPLACE] FUNCTION func_name
[([IN] param_name [=] param_type [WITH | NOT DEFAULT] [WITH | NOT NULL]
{, [IN] param_name [=] param_type [WITH | NOT DEFAULT] [WITH | NOT NULL]})]
RETURN (result_type) =|AS
[
[declare_section] BEGIN statement {; statement}[;] RETURN expression; END
|
LANGUAGE language_name language_specific_attribute = value
{,lang_specific_attribute = value}
]
CREATE OR REPLACE
Recreates the function if it already exists and preserves the object ID and any existing dependent objects.
func_name
Defines the name of the function. This must be a valid object name. Function names are case insensitive and cannot be delimited.
IN
Declares the parameter as an input only parameter. IN is the only supported mode.
param_name
Defines the name of a function parameter. This must be a valid object name.
param_type
Specifies the data type of the associated parameter. The data type can be any legal SQL data type, and the WITH | NOT NULL clause can be part of the specification.
result_type
Specifies the data type of the intended RETURN statement. The data type can be any legal SQL data type.
declare_section
(SQL language only) Describes a list of local variables for use in the function. For details, see
DECLARE.
statement
(SQL language only) Defines local variable assignments, simple SELECT assign statements, and RETURN statement. Non-scalar SELECT expressions and control flow instructions like FOR or WHILE cannot be used.
LANGUAGE=language_name
Specifies the implementation language. Valid values are:
JAVASCRIPT (or JS) – JavaScript
PYTHON (or PY) – Python
SCALA (or SC) - Scala
SQL – SQL
To define an SQL UDF, use the DECLARE-BEGIN-END SQL syntax.
lang_specific_attribute=value
An attribute defining a characteristic from the table. Valid values are:
Description
The CREATE FUNCTION statement creates a user-defined function (UDF). The function is executed in the chosen language by either the DBMS Server for Ingres tables or by the X100 Server for X100 tables.
Function names can be overloaded and are differentiated by the data types of the parameters. For choosing which overloaded function instance to execute, data type size is generally ignored. For creating functions, however, the size of integers and floating-point numbers and the scale and precision of decimals are treated as distinct. For all other data types, size is ignored. Thus, functions fn(int) and fn(smallint) are treated as distinct but fn(varchar(10)) and fn(varchar(20)) are treated as the same.
DBA owned Shared UDFs
Users can access the shared pool of UDFs created by the DBA and also call the DBA UDFs. Both the user UDFs and DBA UDFs share the same name space. DBA UDFs can securely have priority over user UDFs of the same signature and a user cannot override a matching DBA UDF by default.
Overridable UDF can be created with the addition of the PRIORITY=FALSE qualifier on the DBA CREATE FUNCTION. DBA UDFs can be defined to be overridden by user UDFs of the same signature thus allowing access to default instances of shared UDFs. DBA UDF priority can be used with each of the supported UDF languages.
Parameter Modes
Parameters to a user-defined function are INPUT only. Though the parameter value may be updated in the body of the function, the changed value is not passed back to the calling application, rule, or procedure.
Nullability and Default Values for Parameters
User-defined functions can be called from embedded SQL applications or from interactive SQL. The caller supplies values for function parameters. The WITH DEFAULT, NOT DEFAULT, WITH NULL, and NOT NULL clauses can be used to specify whether parameters have default values and whether they are nullable.
Note: Since Vector does not support embedded NULLs in char or varchar fields, returning a Unicode string with the code point U0000 will result in the field being truncated.
These clauses have the following meanings for function parameters:
WITH DEFAULT
The caller does not have to specify a value for the parameter. If the parameter is nullable, its default value is null. If the parameter is not nullable, its default value is 0 (for numeric data types) or blanks (for character data types).
NOT DEFAULT
The caller must specify a value for the parameter. If no value is specified, the DBMS Server issues an error.
WITH NULL
The parameter can be null.
NOT NULL
The parameter cannot be null.
The combined effects of these clauses are as follows:
Permissions
To use CREATE FUNCTION, you must have permission to access the tables and views specified in queries issued by the function.
Embedded Usage
The embedded CREATE FUNCTION statement is identical to its interactive version, with the following exceptions and additions for the SQL language:
• Braces { } cannot be used in place of the BEGIN and END clauses.
• All statements in the function must be separated by semicolons. The statement terminator after the final END clause follows the syntax of the host language.
• The CREATE FUNCTION statement cannot contain any references to host language variables.
• The rules for the continuation of statements over multiple lines follow the embedded SQL host language rules. String literals, continued over multiple lines, also follow the host language rules.
• Comments in a procedure body follow the comment rules of the host language.
The SQL syntax of the CREATE FUNCTION statement is validated at runtime, not by the preprocessor.
Related Statements
DROP FUNCTION
DECLARE