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)