Creates a User Defined Scalar 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 FairCom DB SQL Guide to Using Stored Procedures and Triggers and User Defined Functions.
CREATE FUNCTION [ IF NOT EXISTS ] [ owner_name.]function_name
( [parameter_decl , ... ] )
[ IN ] parameter_name data_type Arguments
IF NOT EXISTS
This argument avoids failure by creating the function only if a function of the same name does not already exist.
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.
Names the user defined function. DROP FUNCTION statements specify the function_name defined here. FairCom DB SQL also uses function_name in the name of the Java class it creates from the Java snippet.
The return value of the function as defined by the data_type declaration.
Specifies standard Java classes to import. The IMPORT keyword must be upper case and on a separate line.
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 FairCom DB SQL creates and submits to the Java compiler.
The BEGIN and END keywords must be upper case and on separate lines.
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))
String new_str = org_string + string_to_concat ;
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.
SQL-93, ODBC Core SQL grammar
Embedded SQL, interactive SQL, ODBC and JDBC applications