CREATE FUNCTION

Description

Creates a User Defined Scaler Function (UDF) for the specified table. User Defined Scalar Functions are an extension to the existing built-in scalar functions and return a single value each time one is invoked. These functions can be used in queries in the same way that system defined scalar functions are used. UDFs are written with Java source code. For more detail on creating and using triggers, see the c-treeACE SQL Guide to Using Stored Procedures and Triggers and User Defined Functions.

Syntax

CREATE FUNCTION [ owner_name.]function_name

         ( [parameter_decl , … ] )

         RETURNS (data_type)

        [ IMPORT

                    java_import_clause ]

        BEGIN

            java_snippet

        END

parameter_decl ::

        [ IN ] parameter_name data_type Arguments

owner_name

Specifies the owner of the user defined function. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

function_name

Names the user defined function. DROP FUNCTION statements specify the function_name defined here. c-treeACE SQL also uses function_name in the name of the Java class it creates from the Java snippet.

RETURNS
data_type

The return value of the function as defined by the data_type declaration.

IMPORT
java_import_clause

Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.

BEGIN
java_snippet

END

The body of the function. The body contains the Java source code that implements the user defined function. The Java statements become a method in a class that c-treeACE SQL creates and submits to the Java compiler.

The BEGIN and END keywords must be upper case and on separate lines.

Notes

• User Defined Scalar Function are a type of c-treeACE SQL expression that return a value based on the argument(s) supplied. User Defined Scalar Function are invoked in exactly the same manner as built in scalar functions.
• User Defined Scalar Functions can be used in the SELECT list or in the WHERE clause. They can be used as parameters of other scalar functions or in any expression. The parameter passed to a user defined scalar function can be a literal, field reference or any expression.

Example

The following example creates a User Defined Scalar Function named ‘str_cat’ that takes two input arguments and returns the concatenated string.

CREATE FUNCTION str_cat(IN org_string VARCHAR(20), IN string_to concat VARCHAR(20))

     RETURNS VARCHAR(40)

IMPORT

     import java.math.*;

BEGIN

     String new_str = org_string +  string_to_concat ;

     return new_str;

END

Authorization

Users executing CREATE FUNCTION must have the DBA privilege or RESOURCE privilege. The owner or users with the DBA privilege can execute or drop any User Defined Scalar Function, and grant the EXECUTE privilege to other users.

When a User Defined Scalar Function is executed on behalf of a user with EXECUTE privilege on that User Defined Scalar Function, for the objects that are accessed by the User Defined Scalar Function, the User Defined Scalar Function owner’s privileges are checked and not the user’s. This enables a user to execute a User Defined Scalar Function successfully even when he does not have the privileges to directly access the objects that are accessed by the User Defined Scalar Function, so long as he has EXECUTE privilege on the User Defined Scalar Function.