Teiid Designer enables users to extend Teiid's scalar or aggregate function library via modeling and definition of custom or User Defined Functions(UDFs).
The following are used to define a UDF.
Function Name - When you create the function name, keep these requirements in mind:
You cannot overload existing Teiid System functions.
The function name must be unique among user-defined functions in its model 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 cannot contain the '.' character.
The function name cannot exceed 255 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. If REQUIRED is used, then user must extend the Translator for the source and add this function to its pushdown function library.
- invocationClass/invocationMethod- optional properties indicating the 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. Defaults to false. It is important to mark the function as deterministic if it returns the same value for the same inputs as this will lead to better performance. See also the Relational extension boolean metadata property "deterministic" and the DDL OPTION property "determinism". Defaults to false. It is important to mark the function as deterministic if it returns the same value for the same inputs as this will lead to better performance. See also the Relational extension boolean metadata property "deterministic" and the DDL OPTION property "determinism".
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.
Teiid Designer provides a new object wizard to assist in modeling your UDF. Simply right-click select a model and right-click select New > Procedure action. In the wizard there will be an option to specify the functions's jar location on your file system, as well as the java class and java method. (See Section 6.2.2, “Create Relational Procedure Wizard” for further information)
If creating a function by other means, you'll need to select the procedure and edit the different properties for the procedure in the Properties view.
A User Defined Function represents a defined method in a java class. In order for the Teiid runtime to register the function and allow it's use in your transformations, you'll need to specify the following as properties in the figure above:
Function Category - unique name for a grouping of user-defined functions. This category will be selectable in the Expression Builder wizard so you can locate and select your function.
Java Class - fully qualified name of the java class containing your function method.
Java Method - the name of the function method in your java class.
UDF Jar Path - the relative path in your project for the jar containing your UDF which will be in a lib folder in your project. If you are defining your jar for the first time and there is no jars in your lib folder, the property editor will allow you to select a jar from your file system. If one or more jars already exist in a lib folder, the editor will allow you to choose between selecting from your workspace or your file system.
Once you've modeled your functions you can use your function calls in your transformation SQL. These functions will be accessible through the Transformation Editor's expression builder wizard, just like any other built-in function. (See Section 6.3.1.4, “Using the Expression Builder” for further information)
In order for Teiid to become aware of your functions, the actual code must be deployed on your server and available to your Teiid submodule. Your Teiid Designer workspace is aware of any models containing functions and their referenced jars and class info. When a source model containing UDFs is added to a VDB, the jar containing the defined function will be added to your VDB and visible in the VDB Editor's UDF Jars tab.
Function models have been deprecated and tooling support to create or edit them will be removed in Teiid Designer 11.0. In 10.0, we've added a feature to convert the functions in your legacy function models into User Defined Functions (virtual procedures) in a target view model.
To convert your function model:
Step 1 - In the Model Explorer select your function model and select the Modeling > Convert Legacy Functions to Virtual Procedures action.
Step 2 - In the dialog, check the functions you wish to convert and select the target virtual model and click OK>.
The selected scalar functions are now defined as virtual model User Defined Functions and available to use in your transformations
