JBoss.orgCommunity Documentation
If you need to extends Teiid's scalar function library, then Teiid provides a means to define custom scalar functions or User Defined Functions(UDF). The following steps need to be taken in creating a UDF.
The FunctionDefinition.xmi file provides metadata to the query engine on User Defined Functions. See the Designer Documentation for more on creating a Function Definition Model.
The following are used to define a UDF.
Function Name When you create the function name, keep these requirements in mind:
You cannot use a reserved word, which includes existing Teiid System function names. You cannot overload existing Teiid System functions.
The function name must be unique among user-defined functions for the number of arguments. You can use the same function name for different numbers of types of arguments. Hence, you can overload your user-defined functions.
The function name can only contain letters, numbers, and the underscore (_). Your function name must start with a letter.
The function name cannot exceed 128 characters.
Input Parameters - defines a type specific signature list. All arguments are considered required.
Return Type - the expected type of the returned scalar value.
Pushdown - can be one of REQUIRED, NEVER, ALLOWED. Indicates the expected pushdown behavior. If NEVER or ALLOWED are specified then a Java implementation of the function should be supplied.
invocationClass/invocationMethod - optional properties indicating the static method to invoke when the UDF is not pushed down.
Deterministic - if the method will always return the same result for the same input parameters.
Even pushdown required functions need to be added as a UDF to allow Teiid to properly parse and resolve the function. Pushdown scalar functions differ from normal user-defined functions in that no code is provided for evaluation in the engine. An exception will be raised if a pushdown required function cannot be evaluated by the appropriate source.
While Teiid provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a user defined function allows for calling a source specific function.
For example, suppose you want to use the Oracle-specific functions score and contains:
SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0
The
score
and
contains
functions are not part of built-in scalar function library. While
you could write your own custom scalar function to mimic their
behavior, it's more likely that you would want to use the actual
Oracle functions that are provided by Oracle when using the Oracle
Free Text functionality.
In addition to the normal steps outlined in the section to create and install a function model (FunctionDefinitions.xmi), you will need to extend the appropriate connector(s).
For example, to extend the Oracle Connector
Required
- extend the OracleExecutionFactory and add SCORE and CONTAINS as
supported functions. For this example, we'll call the class
MyOracleExecutionFactory. Add the
org.teiid.translator.Translator
annotation to the class, e.g.
@Translator(name="myoracle")
Optionally register new FunctionModifiers on the start of the ExecutionFactory to handle translation of these functions. Given that the syntax of these functions is same as other typical functions, this probably isn't needed - the default translation should work.
Create a new translator jar containing your custom ExecutionFactory. See packaging and deployment instructions for using the jar.
Non-pushdown support requires a Java function that matches the metadata supplied in the FunctionDefinitions.xmi file. You must create a Java method that contains the function’s logic. This Java method should accept the necessary arguments, which the Teiid System will pass to it at runtime, and function should return the calculated or altered value.
Code Requirements
The java class containing the function method must be defined public.
The function method must be public and static.
Number of input arguments must match the function metadata defined in section Install user-defined functions
Any exception can be thrown, but Teiid will rethrow the exception
as a
FunctionExecutionException
.
Example 5.1. Sample code
package org.something;
public class TempConv {
/**
* Converts the given Celsius temperature to Fahrenheit, and returns the
* value.
* @param doubleCelsiusTemp
* @return Fahrenheit
*/
public static Double celsiusToFahrenheit(Double doubleCelsiusTemp){
if (doubleCelsiusTemp == null) {
return null;
}
return (doubleCelsiusTemp)*9/5 + 32;
}
}