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. 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 After coding the functions, compile the Java code into a Java Archive (JAR) file. Create a JBoss EAP module ( module.xml ) accompanying the JAR file in the EAP_HOME /modules/ directory. Add the module dependency to the DATABASE -vdb.xml file as shown in the example below. 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.

Commands:
<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>
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; } }
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; } }
package org.something; public class SessionInfo { /** * @param context * @return the created Timestamp */ public static Timestamp sessionCreated(CommandContext context) { return new Timestamp(context.getSession().getCreatedTime()); } }
<vdb name="{vdb-name}" version="1"> <property name ="lib" value ="{module-name}"></property> </vdb>