CREATE FUNCTION
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE FUNCTION statement creates a user-defined function (UDF).
Note: SQL and JavaScript UDFs are a development release. Please work with Actian Support before using such UDFs in a production environment.
Python UDFs are in beta status and disabled by default. Do not use Python UDFs in production until Actian certifies this functionality as production ready. Please contact Actian Support if you are interested in beta testing.
The CREATE FUNCTION 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.
Note: You can create SQL UDFs only with the
Actian SQL CLI, except for simple functions that require no DECLARE section for local variables.
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 more information, 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
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 the Actian Data Platform.
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.
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.
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, Actian Data Platform 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