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"