Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

7.2. Support for Non-Pushdown User Defined Functions

To define a non-pushdown function, a Java function must be provided that matches the metadata supplied either in the Teiid Designer or Dynamic VDB defined metadata. User Defined Function (or UDF) and User Defined Aggregate Function (or UDAF) may be called at runtime like any other function or aggregate function respectively.

7.2.1. Non-Pushdown UDF Metadata in Teiid Designer

You can create a user-defined function on any VDB in a view model. To do so, create a function as a base table. Make sure you provide the JAVA code implementation details in the properties dialog for the UDF.

7.2.2. Non-Pushdown UDF Metadata for Dynamic VDBs

When defining the metadata using DDL in the Dynamic VDBs, user can define a UDF or UDAF (User Defined Aggregate Function) as shown below. 
<vdb name="{vdb-name}" version="1">
    <model name="{model-name}" type="VIRTUAL">
         <metadata type="DDL"><![CDATA[
             CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius decimal) RETURNS decimal OPTIONS (JAVA_CLASS 'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');
             CREATE VIRTUAL FUNCTION sumAll(arg integer) RETURNS integer OPTIONS (JAVA_CLASS 'org.something.SumAll',  JAVA_METHOD 'addInput', AGGREGATE 'true', VARARGS 'true', "NULL-ON-NULL" 'true');]]> </metadata>
    </model>
</vdb>
You must create a Java method that contains the function's logic. This Java method should accept the necessary arguments, which Red Hat JBoss Data Virtualization will pass to it at runtime, and function should return the calculated or altered value.
Refer to the Red Hat JBoss Data Virtualization Development Guide: Reference Material for more information about DDL Metadata and options related to functions defined via DDL.

7.2.3. Coding Non-Pushdown Functions

7.2.3.1. UDF Coding

The following are requirements for coding User Defined Functions (UDFs):
  • The Java class containing the function method must be defined public.

    Note

    You can declare multiple user defined functions for a given class.
  • The function method must be public and static.

Example 7.1. Sample UDF 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;
   }
}

7.2.3.2. UDAF Coding

The following are requirements for coding User Defined Aggregate Functions (UDAFs):
  • The Java class containing the function method must be defined public and extend org.teiid.UserDefinedAggregate.
  • The function method must be public.

Example 7.2. Sample UDAF Code

package org.something;

public class SumAll implements UserDefinedAggregate<Integer> {

        private boolean isNull = true;
        private int result;

        public void addInput(Integer... vals) {
                isNull = false;
                for (int i : vals) {
                        result += i;
                }
        }

        @Override
        public Integer getResult(org.teiid.CommandContext commandContext) {
                if (isNull) {
                        return null;
                }
                return result;
        }

        @Override
        public void reset() {
                isNull = true;
                result = 0;
        }

}

7.2.3.3. Coding: Other Considerations

The following are additional considerations when coding UDFs or UDAFs:
  • Number of input arguments and types must match the function metadata defined in Section 7.1, “User Defined Functions”.
  • Any exception can be thrown, but Red Hat JBoss Data Virtualization will throw the exception as a FunctionExecutionException.
  • You may optionally add an additional org.teiid.CommandContext argument as the first parameter. The CommandContext interface provides access to information about the current command, such as the executing user, subject, the VDB, the session id, etc. This CommandContext parameter should not be declared in the function metadata.

Example 7.3. Sample CommandContext Usage

package org.something;

public class SessionInfo 
{
   /**
   * @param context 
   * @return the created Timestamp 
   */
   public static Timestamp sessionCreated(CommandContext context)
   {
      return new Timestamp(context.getSession().getCreatedTime());
   }
}
The corresponding user-defined function would be declared as Timestamp sessionCreated().

7.2.3.4. Post Coding Activities

  1. After coding the functions, compile the Java code into a Java Archive (JAR) file.
  2. Create a JBoss EAP module (module.xml) accompanying the JAR file in the EAP_HOME/modules/ directory.
  3. Add the module dependency to the DATABASE-vdb.xml file as shown in the example below. 
    <vdb name="{vdb-name}" version="1">
    <property name ="lib" value ="{module-name}"></property>
     ...
</vdb>
    The lib property value may contain a space delimited list of module names if more than one dependency is needed.

    Note

    Alternatively, when using a VDB created with Teiid Designer (DATABASE.vdb), the JAR file may be placed in your VDB under the /lib directory. It will be added automatically to the VDB classloader.