Support for User-defined Functions (Non-pushdown)

To define a non-pushdown function, a Java function matching the VDB-defined metadata must be provided. A user-defined function (or UDF) and a user-defined aggregate function (or UDAF) may be called at runtime, just like any other function or aggregate function, respectively.

Function Metadata

Remember to provide the Java code implementation details in the properties dialogue for the UDF. You can define a UDF or UDAF as shown below:

SQL

CREATE FUNCTION views.md5(p string) RETURNS string OPTIONS (JAVA_CLASS 'com.datavirtuality.dv.core.teiid.md5.MD5Handler', JAVA_METHOD 'calculate') ;;

SELECT views.md5('test') FROM test_tables_pg.test_d;;

SELECT views.md5(e) FROM test_tables_pg.test_d;;

SELECT views.md5('test') ;;

You must create a Java method that contains the function’s logic. This Java method should accept the necessary arguments, which the CData Virtuality Server will pass to it at runtime, and the function should return the calculated or altered value.

Writing the Java Code Required by the UDF

The number of input arguments and types must match the function metadata defined in the VDB metadata.

Code Requirements for UDFs

The java class containing the function method must be defined as public;

One implementation class can contain more than one UDF implementation method.

The function method must be public and static.

Example

XML

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;
   }
}

Code Requirements For UDAFs

The java class containing the function method must be defined as public and extend org.teiid.UserDefinedAggregate;
The function method must be public.

Example

XML

package org.something;

public static 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;
    }

}

Other Considerations

Any exception can be thrown, but the CData Virtuality Server will rethrow 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, VDB, session id, etc. This CommandContext parameter should not be declared in the function metadata.

Example Using CommandContext

XML

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 UDF will be declared as timestamp sessionCreated().

Post-code Activities

After coding the functions, compile the Java code into a Java Archive (JAR) file, add it to the CData Virtuality Server as a custom module, and include this module as a dependency to the main teiid module;
The CData Virtuality Server should be stopped before modifying the module configuration and restarted afterwards.

Adding Custom Module

Create a WildFly module by placing the JAR file in the <dvserver-folder>/modules directory (e.g. <dvserver-folder>/modules/system/layers/base/udfexample/main) and create a module.xml file in the same folder as shown below (we are supposing the jar file is named udfexample.jar):

XML

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="udfexample">
  <resources>
    <resource-root path="udfexample.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

Adding Module Dependency

To load the module on server start, add it as a dependency to the main teiid module by modifying the /modules/system/layers/base/org/jboss/teiid/main/module.xml file. Find the <dependencies> section and add your module as shown below:

XML

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.jboss.teiid">
    <resources>
        .
        .
        .
    </resources>

    <dependencies>
        <module name="javax.api" />
        .
        .
        .
	    <module name="udfexample"/>
    </dependencies>
</module>