Python Example: add2ints
The add2ints
scalar function adds two integers together, returning a single integer result.
You can find more UDx examples in the VerticaGithub repository, https://github.com/vertica/UDx-Examples.
UDSF Python Code
import vertica_sdk class add2ints(vertica_sdk.ScalarFunction): """Return the sum of two integer columns""" def __init__(self): pass def setup(self, server_interface, col_types): pass def processBlock(self, server_interface, arg_reader, res_writer): # Writes a string to the UDx log file. server_interface.log("Python UDx - Adding 2 ints!") while(True): # Example of error checking best practices. if arg_reader.isNull(0) or arg_reader.isNull(1): raise ValueError("I found a NULL!") else: first_int = arg_reader.getInt(0) # Input column second_int = arg_reader.getInt(1) # Input column res_writer.setInt(first_int + second_int) # Sum of input columns. res_writer.next() # Read the next row. if not arg_reader.next(): # Stop processing when there are no more input rows. break def destroy(self, server_interface, col_types): pass class add2ints_factory(vertica_sdk.ScalarFunctionFactory): def createScalarFunction(self, srv): return add2ints() def getPrototype(self, srv_interface, arg_types, return_type): arg_types.addInt() arg_types.addInt() return_type.addInt() def getReturnType(self, srv_interface, arg_types, return_type): return_type.addInt()
Load the Function and Library
Create the library and the function.
=> CREATE LIBRARY pylib AS '/home/dbadmin/python_udx/add2ints/add2ints.py' LANGUAGE 'Python'; CREATE LIBRARY => CREATE FUNCTION add2ints AS LANGUAGE 'Python' NAME 'add2ints_factory' LIBRARY pylib fenced; CREATE FUNCTION
Querying Data with the Function
The following shows how to run a query with the UDSF.
=> SELECT add2ints(10, 10); add2ints ---------- 20 (1 row)
You can query on a table with two integer columns as follows:
=> SELECT numbs_1, numbs_2, add2ints(numbs_1, numbs_2) AS add2ints_sum FROM bunch_of_numbers; numbs_1 | numbs_2 | add2ints_sum ---------+---------+-------------- 10 | 10 | 20 1 | 4 | 5 6 | 6 | 12 30 | 144 | 174 (4 rows)