CREATE FUNCTION

CREATE FUNCTION — define a user defined function (UDF)

Syntax

CREATE FUNCTION [schema.]name 
    [ ( arg_name arg_type [ ,arg_name arg_type ]... ) ]
    RETURNS 
    {
        return_data_type
    |
        TABLE table_name ( column_name column_type [, column_name column_type ]... )
    }
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'jar_id:method_name'
|
    AS
        sql_code_block
    END_FUNCTION;
}

Description

The CREATE FUNCTION statement is used to create user defined functions. There are two types of functions.

CREATE FUNCTION may also be used to create stored procedures written in Java. See Language below and Using Embedded Java Stored Procedures.

Only users assigned the system DBA role can create a function (see GRANT). If schema is provided, then the function is created in the specified schema. Otherwise, it is created in the current schema. The name of the function must not match any existing function with the same number of input arguments in the same schema. However, multiple functions with the same name can be defined in the same schema if they contain different numbers of arguments. This is called overloading (see Example 2).

To replace the current definition of an existing function use ALTER FUNCTION. To remove the function use DROP FUNCTION. (See ALTER FUNCTION, DROP FUNCTION.)

The user that creates the function becomes the owner of the function. The owner must use the GRANT statement to give ALTER and EXECUTE privileges to other users in order for them to be able to change or execute the function. Any user that has permission to alter a function can also remove the function. If a user drops and recreates a function then the new function is not the same entity as the old function. That user is the owner of the recreated function and must GRANT appropriate privileges to other users.

Functions are not case sensitive. For example, a function created with the name Fnc_ToDo can be called by select FNC_TODO() from dual.

Arguments

Each argument to a user defined function is implicitly declared as an input argument, and will not be modified by the UDF.

Return Values

The RETURNS parameter is required. If a function returns a single data type value, it is a scalar function. If a function returns a table, it is a table function. A function defined as a table function will return multiple rows that contain multiple values.

A scalar function returns a simple data type or an expression. This can be used in a SELECT statement and/or this can be used in a WHERE clause.

A table function is used in the FROM clause of a SELECT statement. There are two ways to return the table, RETURN (SELECT...) which creates a streaming result set that fetches the rows on demand and INSERT INTO... with a separate RETURN statement which will return the table once all the data is inserted. When using a table function, you cannot use the same function multiple times as items in the FROM clause, even if the parameter values being passed to the function are different.

Example 5 illustrates returning the table using RETURN (SELECT...). There are times when a single SELECT statement cannot generate the result set. Example 6 illustrates returning the table using INSERT INTO... and a separate RETURN statement once all of the data is inserted. There can be one or more INSERT INTO statements prior to the RETURN statement. This materializes the entire table in memory. The SQL system property MAX_MATERIALIZED_QUERY_SIZE sets the maximum amount of bytes that a materialized query can use. The default is 64 MB.

Deterministic versus Not Deterministic

A deterministic UDF means that the function is guaranteed to return the same results given the same arguments for all rows within a single statement. A UDF is not deterministic when it may return different results on successive calls with the same arguments. A query using a UDF declared with NOT DETERMINISTIC will re-evaluate the function at every row. The default is NOT DETERMINISTIC.

See Cache below.

Language

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See NuoDB SQL Procedural Language for more information.

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See Using Embedded Java Stored Procedures.

Security

You may declare a stored procedure or user defined function to work in two different ways with respect to the calling user's privileges on database objects affected. The procedure or function may be declared in one of two ways:

SECURITY INVOKER

Checks the privileges of the calling user. If a stored procedure or user defined function is inserting, selecting, and/or updating one or more tables, the calling user has to have the appropriate privilege for the operation on each table.

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to SELECT or INSERT INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

Note: Whoever gets the execution rights on the procedure can access the same tables/schema/sequence/etc.. of the creator of the procedure, even if the current user is still the invoker.

External Definition

Declaring a UDF as EXTERNAL is done to describe a dynamically loadable Java function that is to be executed. For LANGUAGE JAVA, the EXTERNAL function defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the CREATE JAVACLASS command used to upload to the NuoDB server the JAR with the defined code. See CREATE JAVACLASS.

SQL Procedural Language - CREATE FUNCTION body

If defining this UDF in SQL, sql_code_block is one or more SQL statements. See NuoDB SQL Procedural Language for more information.

Cache

Deterministic user defined functions are stored in an in-memory cache. The size of this cache is limited by the system property UDF_CACHE_SIZE. (See SQL System Properties.) The cache stores any type of user defined function, regardless of how it is implemented (Java or SQL). A deterministic user defined function is one that will return the same result when it is run with the same arguments. When the cache is full, the oldest entry gets removed, so if that combination of arguments is used again, the user defined function code will be executed to compute the result. The default is 50. An example is as follows:

System Tables

The body of the function is stored in SYSTEM.FUNCTIONS in the FUNCTIONTEXT column. See FUNCTIONS System Table Description and Example 1 below.

Parameters

Note: When using the nuosql command line utility to create or alter multi-statement functions, the SET DELIMITER command must be used prior to defining the function. This new delimiter is used to indicate the end of the function definition and will execute the CREATE FUNCTION statement. See the examples below.

Examples

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.