This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

UDx parameters

Parameters let you define arguments for your UDxs that remain constant across all of the rows processed by the SQL statement that calls your UDx.

Parameters let you define arguments for your UDxs that remain constant across all of the rows processed by the SQL statement that calls your UDx. Typically, your UDxs accept arguments that come from columns in a SQL statement. For example, in the following SQL statement, the arguments a and b to the add2ints UDSF change value for each row processed by the SELECT statement:

=> SELECT a, b, add2ints(a,b) AS 'sum' FROM example;
a | b  | sum
---+----+-----
1 |  2 |   3
3 |  4 |   7
5 |  6 |  11
7 |  8 |  15
9 | 10 |  19
(5 rows)

Parameters remain constant for all the rows your UDx processes. You can also make parameters optional so that if the user does not supply it, your UDx uses a default value. For example, the following example demonstrates calling a UDSF named add2intsWithConstant that has a single parameter value named constant whose value is added to each the arguments supplied in each row of input:

=> SELECT a, b, add2intsWithConstant(a, b USING PARAMETERS constant=42)
    AS 'a+b+42' from example;
a | b  | a+b+42
---+----+--------
1 |  2 |     45
3 |  4 |     49
5 |  6 |     53
7 |  8 |     57
9 | 10 |     61
(5 rows)

The topics in this section explain how to develop UDxs that accept parameters.

1 - Defining UDx parameters

You define the parameters that your UDx accepts in its factory class (ScalarFunctionFactory, AggregateFunctionFactory, and so on) by implementing getParameterType().

You define the parameters that your UDx accepts in its factory class (ScalarFunctionFactory, AggregateFunctionFactory, and so on) by implementing getParameterType(). This method is similar to getReturnType(): you call data-type-specific methods on a SizedColumnTypes object that is passed in as a parameter. Each function call sets the name, data type, and width or precision (if the data type requires it) of the parameter.

Setting parameter properties (C++ only)

When you add parameters to the getParameterType() function using the C++ API, you can also set properties for each parameter. For example, you can define a parameter as being required by the UDx. Doing so lets the Vertica server know that every UDx invocation must provide the specified parameter, or the query fails.

By passing an object to the SizedColumnTypes::Properties class, you can define the following four parameter properties:

Parameter Type Description
visible BOOLEAN If set to TRUE, the parameter appears in the USER_FUNCTION_PARAMETERS table. You may want to set this to FALSE to declare a parameter for internal use only.
required BOOLEAN

If set to TRUE:

  • The parameter is required when invoking the UDx.
  • Invoking the UDx without supplying the parameter results in an error, and the UDx does not run.
canBeNull BOOLEAN

If set to TRUE, the parameter can have a NULL value.

If set to FALSE, make sure that the supplied parameter does not contain a NULL value when invoking the UDx. Otherwise, an error results, and the UDx does not run.

comment VARCHAR(128)

A comment to describe the parameter.

If you exceed the 128 character limit, Vertica generates an error when you run the CREATE_FUNCTION command. Additionally, if you replace the existing function definition in the comment parameter, make sure that the new definition does not exceed 128 characters. Otherwise, you delete all existing entries in the USER_FUNCTION_PARAMETERS table related to the UDx.

Setting parameter properties (R only)

When using parameters in your R UDx, you must specify a field in the factory function called parametertypecallback. This field points to the callback function that defines the parameters expected by the function. The callback function defines a four-column data frame with the following properties:

Parameter Type Description
datatype VARCHAR(128) The data type of the parameter.
length INTEGER The dimension of the parameter.
scale INTEGER The proportional dimensions of the parameter.
name VARCHAR(128) The name of the parameter.

If any of the columns are left blank (or the parametertypecallback function is omitted), then Vertica uses default values.

For more information, see Parametertypecallback function.

Redacting UDx parameters

If a parameter name meets any of the following criteria, its value is automatically redacted from logs and system tables like QUERY_REQUESTS:

  • Named "secret" or "password"

  • Ends with "_secret" or "_password"

2 - Getting parameter values in UDxs

Your UDx uses the parameter values it declared in its factory class (see Defining the Parameters Your UDx Accepts) in its function class's processing method (for example, processBlock() or processPartition()).

Your UDx uses the parameter values it declared in its factory class (see Defining UDx parameters) in its function class's processing method (for example, processBlock() or processPartition()). It gets its parameter values from a ParamReader object, which is available from the ServerInterface object that is passed to your processing method. Reading parameters from this object is similar to reading argument values from BlockReader or PartitionReader objects: you call a data-type-specific function with the name of the parameter to retrieve its value. For example, in C++:

// Get the parameter reader from the ServerInterface to see if there are supplied parameters.
ParamReader paramReader = srvInterface.getParamReader();
// Get the value of an int parameter named constant.
const vint constant = paramReader.getIntRef("constant");

Using parameters in the factory class

In addition to using parameters in your UDx function class, you can also access the parameters in the factory class. You may want to access the parameters to let the user control the input or output values of your function in some way. For example, your UDx can have a parameter that lets the user choose to have your UDx return a single- or double-precision value. The process of accessing parameters in the factory class is the same as accessing it in the function class: get a ParamReader object from the ServerInterface's getParamReader() method, them read the parameter values.

Testing whether the user supplied parameter values

Unlike its handling of arguments, Vertica does not immediately return an error if a user's function call does not include a value for a parameter defined by your UDx's factory class. This means that your function can attempt to read a parameter value that the user did not supply. If it does so, by default Vertica returns a non-existent parameter warning to the user, and the query containing the function call continues.

If you want your parameter to be optional, you can test whether the user supplied a value for the parameter before attempting to access its value. Your function determines if a value exists for a particular parameter by calling the ParamReader's containsParameter() method with the parameter's name. If this call returns true, your function can safely retrieve the value. If this call returns false, your UDx can use a default value or change its processing in some other way to compensate for not having the parameter value. As long as your UDx does not try to access the non-existent parameter value, Vertica does not generate an error or warning about missing parameters.

See C++ example: defining parameters for an example.

3 - Calling UDxs with parameters

You pass parameters to a UDx by adding a USING PARAMETERS clause in the function call after the last argument.

You pass parameters to a UDx by adding a USING PARAMETERS clause in the function call after the last argument.

  • Do not insert a comma between the last argument and the USING PARAMETERS clause.

  • After the USING PARAMETERS clause, add one or more parameter definitions, in the following form:

    <parameter name> = <parameter value>

  • Separate parameter definitions by commas.

Parameter values can be a constant expression (for example 1234 + SQRT(5678)). You cannot use volatile functions (such as RANDOM) in the expression, because they do not return a constant value. If you do supply a volatile expression as a parameter value, by default, Vertica returns an incorrect parameter type warning. Vertica then tries to run the UDx without the parameter value. If the UDx requires the parameter, it returns its own error, which cancels the query.

Calling a UDx with a single parameter

The following example demonstrates how you can call the Add2intsWithConstant UDSF example shown in C++ example: defining parameters:

=> SELECT a, b, Add2intsWithConstant(a, b USING PARAMETERS constant=42) AS 'a+b+42' from example;
 a | b  | a+b+42
---+----+--------
 1 |  2 |     45
 3 |  4 |     49
 5 |  6 |     53
 7 |  8 |     57
 9 | 10 |     61
(5 rows)

To remove the first instance of the number 3, you can call the RemoveSymbol UDSF example:

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3');
  original_string   |   RemoveSymbol
--------------------+-------------------
 3re3mo3ve3sy3mb3ol | re3mo3ve3sy3mb3ol
(1 row)

Calling a UDx with multiple parameters

The following example shows how you can call a version of the tokenize UDTF. This UDTF includes parameters to limit the shortest allowed word and force the words to be output in uppercase. Separate multiple parameters with commas.

=> SELECT url, tokenize(description USING PARAMETERS minLength=4, uppercase=true) OVER (partition by url) FROM T;
       url       |   words
-----------------+-----------
 www.amazon.com  | ONLINE
 www.amazon.com  | RETAIL
 www.amazon.com  | MERCHANT
 www.amazon.com  | PROVIDER
 www.amazon.com  | CLOUD
 www.amazon.com  | SERVICES
 www.dell.com    | LEADING
 www.dell.com    | PROVIDER
 www.dell.com    | COMPUTER
 www.dell.com    | HARDWARE
 www.vertica.com | WORLD'S
 www.vertica.com | FASTEST
 www.vertica.com | ANALYTIC
 www.vertica.com | DATABASE
(16 rows)

The following example calls the RemoveSymbol UDSF. By changing the value of the optional parameter, n, you can remove all instances of the number 3:

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', n=6);
  original_string   | RemoveSymbol
--------------------+--------------
 3re3mo3ve3sy3mb3ol | removesymbol
(1 row)

Calling a UDx with optional or incorrect parameters

You can optionally add the Add2intsWithConstant UDSF's constant parameter. Calling this constraint without the parameter does not return an error or warning:

=> SELECT a,b,Add2intsWithConstant(a, b) AS 'sum' FROM example;
 a | b  | sum
---+----+-----
 1 |  2 |   3
 3 |  4 |   7
 5 |  6 |  11
 7 |  8 |  15
 9 | 10 |  19
(5 rows)

Although calling a UDx with incorrect parameters generates a warning, by default, the query still runs. For further information on setting the behavior of your UDx when you supply incorrect parameters, see Specifying the behavior of passing unregistered parameters.

=> SELECT a, b,  add2intsWithConstant(a, b USING PARAMETERS wrongparam=42) AS 'result' from example;
WARNING 4332:  Parameter wrongparam was not registered by the function and cannot
be coerced to a definite data type
 a | b  | result
---+----+--------
 1 |  2 |      3
 3 |  4 |      7
 5 |  6 |     11
 7 |  8 |     15
 9 | 10 |     19
(5 rows)

4 - Specifying the behavior of passing unregistered parameters

By default, Vertica issues a warning message when you pass a UDx an unregistered parameter.

By default, Vertica issues a warning message when you pass a UDx an unregistered parameter. An unregistered parameter is one that you did not declare in the getParameterType() method.

You can control the behavior of your UDx when you pass it an unregistered parameter by altering the StrictUDxParameterChecking configuration parameter.

Unregistered parameter behavior settings

You can specify the behavior of your UDx in response to one or more unregistered parameters. To do so, set the StrictUDxParameterChecking configuration parameter to one of the following values:

  • 0: Allows unregistered parameters to be accessible to the UDx. The ParamReader class's getType() method determines the data type of the unregistered parameter. Vertica does not display any warning or error message.

  • 1 (default): Ignores the unregistered parameter and allows the function to run. Vertica displays a warning message.

  • 2: Returns an error and does not allow the function to run.

Examples

The following examples demonstrate the behavior you can specify using different values with the StrictUDxParameterChecking parameter.

View the current value of StrictUDxParameterChecking

To view the current value of the StrictUDxParameterChecking configuration parameter, run the following query:


=> \x
Expanded display is on.
=> SELECT * FROM configuration_parameters WHERE parameter_name = 'StrictUDxParameterChecking';
-[ RECORD 1 ]-----------------+------------------------------------------------------------------
node_name                     | ALL
parameter_name                | StrictUDxParameterChecking
current_value                 | 1
restart_value                 | 1
database_value                | 1
default_value                 | 1
current_level                 | DATABASE
restart_level                 | DATABASE
is_mismatch                   | f
groups                        |
allowed_levels                | DATABASE
superuser_only                | f
change_under_support_guidance | f
change_requires_restart       | f
description                   | Sets the behavior to deal with undeclared UDx function parameters

Change the value of StrictUDxParameterChecking

You can change the value of the StrictUDxParameterChecking configuration parameter at the database, node, or session level. For example, you can change the value to '0' to specify that unregistered parameters can pass to the UDx without displaying a warning or error message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 0;
ALTER DATABASE

Invalid parameter behavior with RemoveSymbol

The following example demonstrates how to call the RemoveSymbol UDSF example. The RemoveSymbol UDSF has a required parameter, symbol, and an optional parameter, n. In this case, you do not use the optional parameter.

If you pass both symbol and an additional parameter called wrongParam, which is not declared in the UDx, the behavior of the UDx changes corresponding to the value of StrictUDxParameterChecking.

When you set StrictUDxParameterChecking to '0', the UDx runs normally without a warning. Additionally, wrongParam becomes accessible to the UDx through the ParamReader object of the ServerInterface object:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 0;
ALTER DATABASE

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', wrongParam='x');
  original_string   |   RemoveSymbol
--------------------+-------------------
 3re3mo3ve3sy3mb3ol | re3mo3ve3sy3mb3ol
(1 row)

When you set StrictUDxParameterChecking to '1', the UDx ignores wrongParam and runs normally. However, it also issues a warning message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 1;
ALTER DATABASE

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', wrongParam='x');
WARNING 4320:  Parameter wrongParam was not registered by the function and cannot be coerced to a definite data type
  original_string   |   RemoveSymbol
--------------------+-------------------
 3re3mo3ve3sy3mb3ol | re3mo3ve3sy3mb3ol
(1 row)

When you set StrictUDxParameterChecking to '2', the UDx encounters an error when it tries to call wrongParam and does not run. Instead, it generates an error message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 2;
ALTER DATABASE

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', wrongParam='x');
ERROR 0:  Parameter wrongParam was not registered by the function

5 - User-defined session parameters

User-defined session parameters allow you to write more generalized parameters than what Vertica provides.

User-defined session parameters allow you to write more generalized parameters than what Vertica provides. You can configure user-defined session parameters in these ways:

  • From the client—for example, with ALTER SESSION

  • Through the UDx itself

A user-defined session parameter can be passed into any type of UDx supported by Vertica. You can also set parameters for your UDx at the session level. By specifying a user-defined session parameter, you can have the state of a parameter saved continuously. Vertica saves the state of the parameter even when the UDx is invoked multiple times during a single session.

The RowCount example uses a user-defined session parameter. This parameter counts the total number of rows processed by the UDx each time it runs. RowCount then displays the aggregate number of rows processed for all executions. See C++ example: using session parameters and Java example: using session parameters for implementations.

Viewing the user-defined session parameter

Enter the following command to see the value of all session parameters:

=> SHOW SESSION UDPARAMETER all;
schema | library | key | value
--------+---------+-----+-------
(0 rows)

No value has been set, so the table is empty. Now, execute the UDx:

=> SELECT RowCount(5,5);
RowCount
----------
10
(1 row)

Again, enter the command to see the value of the session parameter:

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 1
(1 row)

The library column shows the name of the library containing the UDx. This is the name set with CREATE LIBRARY. Because the UDx has processed one row, the value of the rowcount session parameter is now 1. Running the UDx two more times should increment the value twice.

=> SELECT RowCount(10,10);
RowCount
----------
20
(1 row)
=> SELECT RowCount(15,15);
RowCount
----------
30
(1 row)

You have now executed the UDx three times, obtaining the sum of 5 + 5, 10 + 10, and 15 + 15. Now, check the value of rowcount.

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 3
(1 row)

Altering the user-defined session parameter

You can also manually alter the value of rowcount. To do so, enter the following command:

=> ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 25;
ALTER SESSION

Check the value of RowCount:

=> SHOW SESSION UDPARAMETER all;
schema |  library  |   key    | value
--------+-----------+----------+-------
public | UDSession | rowcount | 25
(1 row)

Clearing the user-defined session parameter

From the client:

To clear the current value of rowcount, enter the following command:

=> ALTER SESSION CLEAR UDPARAMETER FOR UDSession rowcount;
ALTER SESSION

Verify that rowcount has been cleared:

=> SHOW SESSION UDPARAMETER all;
schema | library | key | value
--------+---------+-----+-------
(0 rows)

Through the UDx in C++:

You can set the session parameter to clear through the UDx itself. For example, to clear rowcount when its value reaches 10 or greater, do the following:

  1. Remove the following line from the destroy() method in the RowCount class:

    udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);

  2. Replace the removed line from the destroy() method with the following code:

    
if (rowCount < 10)
{
udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);
}
else
{
udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
}

  3. To see the UDx clear the session parameter, set rowcount to a value of 9:

    => ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 9;
ALTER SESSION

  4. Check the value of rowcount:

    => SHOW SESSION UDPARAMETER all;
 schema |  library  |   key    | value
--------+-----------+----------+-------
 public | UDSession | rowcount | 9
 (1 row)

  5. Invoke RowCount so that its value becomes 10:

    => SELECT RowCount(15,15);
RowCount
----------
      30
 (1 row)

  6. Check the value of rowcount again. Because the value has reached 10, the threshold specified in the UDx, expect that rowcount is cleared:

    => SHOW SESSION UDPARAMETER all;
 schema | library | key | value
--------+---------+-----+-------
 (0 rows)

    As expected, RowCount is cleared.

Through the UDx in Java:

  1. Remove the following lines from the destroy() method in the RowCount class:

    udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
srvInterface.log("RowNumber processed %d records", count);

  2. Replace the removed lines from the destroy() method with the following code:

    
if (rowCount < 10)
{
udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
srvInterface.log("RowNumber processed %d records", count);
}
else
{
udParams.getUDSessionParamWriter("library").clearParameter("rowCount");
}

  3. To see the UDx clear the session parameter, set rowcount to a value of 9:

    => ALTER SESSION SET UDPARAMETER FOR UDSession rowcount = 9;
ALTER SESSION

  4. Check the value of rowcount:

    => SHOW SESSION UDPARAMETER all;
 schema |  library  |   key    | value
--------+-----------+----------+-------
 public | UDSession | rowcount | 9
 (1 row)

  5. Invoke RowCount so that its value becomes 10:

    => SELECT RowCount(15,15);
RowCount
----------
       30
 (1 row)

  6. Check the value of rowcount. Since the value has reached 10, the threshold specified in the UDx, expect that rowcount is cleared:

    => SHOW SESSION UDPARAMETER all;
 schema | library | key | value
--------+---------+-----+-------
 (0 rows)

As expected, rowcount is cleared.

Read-only and hidden session parameters

If you don't want a parameter to be set anywhere except in the UDx, you can make it read-only. If, additionally, you don't want a parameter to be visible in the client, you can make it hidden.

To make a parameter read-only, meaning that it cannot be set in the client, but can be viewed, add a single underscore before the parameter's name. For example, to make rowCount read-only, change all instances in the UDx of "rowCount" to "_rowCount".

To make a parameter hidden, meaning that it cannot be viewed in the client nor set, add two underscores before the parameter's name. For example, to make rowCount hidden, change all instances in the UDx of "rowCount" to "__rowCount".

Redacted parameters

If a parameter name meets any of the following criteria, its value is automatically redacted from logs and system tables like QUERY_REQUESTS:

  • Named "secret" or "password"

  • Ends with "_secret" or "_password"

See also

Kafka user-defined session parameters

6 - C++ example: defining parameters

The following code fragment demonstrates adding a single parameter to the C++ add2ints UDSF example.

The following code fragment demonstrates adding a single parameter to the C++ add2ints UDSF example. The getParameterType() function defines a single integer parameter that is named constant.

class Add2intsWithConstantFactory : public ScalarFunctionFactory
{
    // Return an instance of Add2ints to perform the actual addition.
    virtual ScalarFunction *createScalarFunction(ServerInterface &interface)
    {
        // Calls the vt_createFuncObj to create the new Add2ints class instance.
        return vt_createFuncObj(interface.allocator, Add2intsWithConstant);
    }
    // Report the argument and return types to Vertica.
    virtual void getPrototype(ServerInterface &interface,
                              ColumnTypes &argTypes,
                              ColumnTypes &returnType)
    {
        // Takes two ints as inputs, so add ints to the argTypes object.
        argTypes.addInt();
        argTypes.addInt();
        // Returns a single int.
        returnType.addInt();
    }
    // Defines the parameters for this UDSF. Works similarly to defining arguments and return types.
    virtual void getParameterType(ServerInterface &srvInterface,
                                  SizedColumnTypes &parameterTypes)
    {
        // One int parameter named constant.
        parameterTypes.addInt("constant");
    }
};
RegisterFactory(Add2intsWithConstantFactory);

See the Vertica SDK entry for SizedColumnTypes for a full list of the data-type-specific functions you can call to define parameters.

The following code fragment demonstrates using the parameter value. The Add2intsWithConstant class defines a function that adds two integer values. If the user supplies it, the function also adds the value of the optional integer parameter named constant.

/**
 * A UDSF that adds two numbers together with a constant value.
 *
 */
class Add2intsWithConstant : public ScalarFunction
{
public:
    // Processes a block of data sent by Vertica.
    virtual void processBlock(ServerInterface &srvInterface,
                              BlockReader &arg_reader,
                              BlockWriter &res_writer)
    {
        try
            {
                // The default value for the constant parameter is 0.
                vint constant = 0;

                // Get the parameter reader from the ServerInterface to see if there are supplied parameters.
                ParamReader paramReader = srvInterface.getParamReader();
                // See if the user supplied the constant parameter.
                if (paramReader.containsParameter("constant"))
                    // There is a parameter, so get its value.
                    constant = paramReader.getIntRef("constant");
                // While we have input to process:
                do
                    {
                        // Read the two integer input parameters by calling the BlockReader.getIntRef class function.
                        const vint a = arg_reader.getIntRef(0);
                        const vint b = arg_reader.getIntRef(1);
                        // Add arguments plus constant.
                        res_writer.setInt(a+b+constant);
                        // Finish writing the row, and advance to the next output row.
                        res_writer.next();
                        // Continue looping until there are no more input rows.
                    }
                while (arg_reader.next());
            }
        catch (exception& e)
            {
                // Standard exception. Quit.
                vt_report_error(0, "Exception while processing partition: %s",
                    e.what());
            }
    }
};

7 - C++ example: using session parameters

The RowCount example uses a user-defined session parameter, also called RowCount.

The RowCount example uses a user-defined session parameter, also called RowCount. This parameter counts the total number of rows processed by the UDx each time it runs. RowCount then displays the aggregate number of rows processed for all executions.

#include <string>
#include <sstream>
#include <iostream>
#include "Vertica.h"
#include "VerticaUDx.h"

using namespace Vertica;

class RowCount : public Vertica::ScalarFunction
{
private:
    int rowCount;
    int count;

public:

    virtual void setup(Vertica::ServerInterface &srvInterface, const Vertica::SizedColumnTypes &argTypes) {
        ParamReader pSessionParams = srvInterface.getUDSessionParamReader("library");
        std::string rCount = pSessionParams.containsParameter("rowCount")?
            pSessionParams.getStringRef("rowCount").str(): "0";
        rowCount=atoi(rCount.c_str());

    }
    virtual void processBlock(Vertica::ServerInterface &srvInterface, Vertica::BlockReader &arg_reader, Vertica::BlockWriter &res_writer) {

        count = 0;
        if(arg_reader.getNumCols() != 2)
            vt_report_error(0, "Function only accepts two arguments, but %zu provided", arg_reader.getNumCols());

        do {
            const Vertica::vint a = arg_reader.getIntRef(0);
            const Vertica::vint b = arg_reader.getIntRef(1);
            res_writer.setInt(a+b);
            count++;
            res_writer.next();
        } while (arg_reader.next());

        srvInterface.log("count %d", count);

        }

        virtual void destroy(ServerInterface &srvInterface, const SizedColumnTypes &argTypes, SessionParamWriterMap &udParams) {
            rowCount = rowCount + count;

            std:ostringstream s;
            s << rowCount;
            const std::string i_as_string(s.str());

            udParams.getUDSessionParamWriter("library").getStringRef("rowCount").copy(i_as_string);

        }
};

class RowCountsInfo : public Vertica::ScalarFunctionFactory {
    virtual Vertica::ScalarFunction *createScalarFunction(Vertica::ServerInterface &srvInterface)
    { return Vertica::vt_createFuncObject<RowCount>(srvInterface.allocator);
    }

    virtual void getPrototype(Vertica::ServerInterface &srvInterface, Vertica::ColumnTypes &argTypes, Vertica::ColumnTypes &returnType)
    {
        argTypes.addInt();
        argTypes.addInt();
        returnType.addInt();
    }
};

RegisterFactory(RowCountsInfo);

8 - Java example: defining parameters

The following code fragment demonstrates adding a single parameter to the Java add2ints UDSF example.

The following code fragment demonstrates adding a single parameter to the Java add2ints UDSF example. The getParameterType() method defines a single integer parameter that is named constant.

package com.mycompany.example;
import com.vertica.sdk.*;
public class Add2intsWithConstantFactory extends ScalarFunctionFactory
{
    @Override
    public void getPrototype(ServerInterface srvInterface,
                             ColumnTypes argTypes,
                             ColumnTypes returnType)
    {
        argTypes.addInt();
        argTypes.addInt();
        returnType.addInt();
    }

    @Override
    public void getReturnType(ServerInterface srvInterface,
                              SizedColumnTypes argTypes,
                              SizedColumnTypes returnType)
    {
        returnType.addInt("sum");
    }

    // Defines the parameters for this UDSF. Works similarly to defining
    // arguments and return types.
    public void getParameterType(ServerInterface srvInterface,
                              SizedColumnTypes parameterTypes)
    {
        // One INTEGER parameter named constant
        parameterTypes.addInt("constant");
    }

    @Override
    public ScalarFunction createScalarFunction(ServerInterface srvInterface)
    {
        return new Add2intsWithConstant();
    }
}

See the Vertica Java SDK entry for SizedColumnTypes for a full list of the data-type-specific methods you can call to define parameters.

9 - Java example: using session parameters

The RowCount example uses a user-defined session parameter, also called RowCount.

The RowCount example uses a user-defined session parameter, also called RowCount. This parameter counts the total number of rows processed by the UDx each time it runs. RowCount then displays the aggregate number of rows processed for all executions.


package com.mycompany.example;

import com.vertica.sdk.*;

public class RowCountFactory extends ScalarFunctionFactory {

    @Override
    public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes, ColumnTypes returnType)
    {
        argTypes.addInt();
        argTypes.addInt();
     returnType.addInt();
    }

public class RowCount extends ScalarFunction {

    private Integer count;
    private Integer rowCount;

    // In the setup method, you look for the rowCount parameter. If it doesn't exist, it is created.
    // Look in the default namespace which is "library," but it could be anything else, most likely "public" if not "library".
    @Override
       public void setup(ServerInterface srvInterface, SizedColumnTypes argTypes) {
    count = new Integer(0);
    ParamReader pSessionParams = srvInterface.getUDSessionParamReader("library");
    String rCount = pSessionParams.containsParameter("rowCount")?
    pSessionParams.getString("rowCount"): "0";
    rowCount = Integer.parseInt(rCount);

    }

    @Override
    public void processBlock(ServerInterface srvInterface, BlockReader arg_reader, BlockWriter res_writer)
        throws UdfException, DestroyInvocation {
        do {
        ++count;
        long a = arg_reader.getLong(0);
        long b = arg_reader.getLong(1);

        res_writer.setLong(a+b);
        res_writer.next();
        } while (arg_reader.next());
    }

    @Override
    public void destroy(ServerInterface srvInterface, SizedColumnTypes argTypes, SessionParamWriterMap udParams){
        rowCount = rowCount+count;
        udParams.getUDSessionParamWriter("library").setString("rowCount", Integer.toString(rowCount));
        srvInterface.log("RowNumber processed %d records", count);
        }
    }

    @Override
    public ScalarFunction createScalarFunction(ServerInterface srvInterface){
        return new RowCount();
    }
}