Java Example: Polymorphic UDx
The following example shows an implementation of a ScalarFunctionFactory
class with an inner ScalarFunction
class that adds together two or more integers.
// You will need to specify the full package when creating functions based on // the classes in your library. package com.mycompany.multiparamexample; // Import the entire Vertica SDK import com.vertica.sdk.*; // Factory class to create polymorphic UDSF that adds all of the integer // arguments it recieves and returns a sum. public class AddManyIntsFactory extends ScalarFunctionFactory { @Override public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes, ColumnTypes returnType) { // Accepts any number and type or arguments. The ScalarFunction // class handles parsing the arguments. argTypes.addAny(); // writes one integer as output returnType.addInt(); } // This polymorphic ScalarFunction adds all of the integer arguments passed // to it. Returns an error if there are less than two arguments, or if one // argument is not an integer. public class AddManyInts extends ScalarFunction { @Override public void processBlock(ServerInterface srvInterface, BlockReader argReader, BlockWriter resWriter) throws UdfException, DestroyInvocation { // See how many arguments were passed in int numCols = argReader.getNumCols(); // Return an error if less than two arguments were given. if (numCols < 2) { throw new UdfException(0, "Must supply at least 2 integer arguments"); } // Make sure all input columns are integer. SizedColumnTypes inTypes = argReader.getTypeMetaData(); for (int param = 0; param < numCols; param++) { VerticaType paramType = inTypes.getColumnType(param); if (!paramType.isInt()) { throw new UdfException(0, "Error: Argument " + (param+1) + " was not an integer. All arguments must be integer."); } } // Process all of the rows of input. do { long total = 0; // Hold the running total of arguments // Get all of the arguments and add them up for (int x = 0; x < numCols; x++) { total += argReader.getLong(x); } // Write the integer output value. resWriter.setLong(total); // Advance the output BlocKWriter to the next row. resWriter.next(); // Continue processing input rows until there are no more. } while (argReader.next()); } } @Override public ScalarFunction createScalarFunction(ServerInterface srvInterface) { // Instantiate the polymorphic UDF class. return new AddManyInts(); } }
The ScalarFunctionFactory.getPrototype()
method calls the addAny()
method to declare that the UDSF is polymorphic.
Most of the work in the example is done by the ScalarFunction.processBlock()
method. It performs two checks on the arguments that have been passed in through the BlockReader
object:
- There are at least two arguments.
- The data type of all arguments are integers.
It is up to your polymorphic UDx to determine that all of the input passed to it is valid.
Once the processBlock()
method validates its arguments, it loops over them, adding them together.
You assign a SQL name to your polymorphic UDx using the same statement you use to assign one to a non-polymorphic UDx. The following demonstration shows how you load and call the polymorphic function from the example.
=> CREATE LIBRARY addmanyintslib AS '/home/dbadmin/AddManyIntsLib.jar' -> LANGUAGE 'Java'; CREATE LIBRARY => CREATE FUNCTION addmanyints AS LANGUAGE 'Java' NAME -> 'com.mycompany.multiparamexample.AddManyIntsFactory' LIBRARY addmanyintslib; CREATE FUNCTION => SELECT addmanyints(1,2,3,4,5,6,7,8,9,10); addmanyints ------------- 55 (1 row) => SELECT addmanyints(1); --Too few parameters ERROR 3399: Failure in UDx RPC call InvokeProcessBlock(): Error in User Defined Object [addmanyints], error code: 0 com.vertica.sdk.UdfException: Must supply at least 2 integer arguments at com.mycompany.multiparamexample.AddManyIntsFactory$AddManyInts.processBlock (AddManyIntsFactory.java:39) at com.vertica.udxfence.UDxExecContext.processBlock(UDxExecContext.java:700) at com.vertica.udxfence.UDxExecContext.run(UDxExecContext.java:173) at java.lang.Thread.run(Thread.java:662) => SELECT addmanyints(1,2,3.14159); --Non-integer parameter ERROR 3399: Failure in UDx RPC call InvokeProcessBlock(): Error in User Defined Object [addmanyints], error code: 0 com.vertica.sdk.UdfException: Error: Argument 3 was not an integer. All arguments must be integer. at com.mycompany.multiparamexample.AddManyIntsFactory$AddManyInts.processBlock(AddManyIntsFactory.java:48) at com.vertica.udxfence.UDxExecContext.processBlock(UDxExecContext.java:700) at com.vertica.udxfence.UDxExecContext.run(UDxExecContext.java:173) at java.lang.Thread.run(Thread.java:662)