JBoss.orgCommunity Documentation
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.
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