Setting Null Input and Volatility Behavior

Normally, Vertica calls your UDSF for every row of data in the query. There are some cases where Vertica can avoid executing your UDSF. You can tell Vertica when it can skip calling your function and just supply a return value itself by changing your function's volatility and strictness settings.

You indicate the volatility and null handling of your function by setting the vol and strict fields in your ScalarFunctionFactory class's constructor.

Volatility Settings

To indicate your function's volatility, set the vol field to one of the following values:

Value Description

VOLATILE

Repeated calls to the function with the same arguments always result in different values. Vertica always calls volatile functions for each invocation.

IMMUTABLE

Calls to the function with the same arguments always results in the same return value.

STABLE

Repeated calls to the function with the same arguments within the same statement returns the same output. For example, a function that returns the current user name is stable because the user cannot change within a statement. The user name could change between statements.

DEFAULT_VOLATILITY

The default volatility. This is the same as VOLATILE.

C++ Example

The following example shows a version of the Add2ints example factory class that makes the function immutable.

class Add2intsImmutableFactory : public Vertica::ScalarFunctionFactory
{
    virtual Vertica::ScalarFunction *createScalarFunction(Vertica::ServerInterface &srvInterface)
    { return vt_createFuncObj(srvInterface.allocator, Add2ints); }
    virtual void getPrototype(Vertica::ServerInterface &srvInterface,
                              Vertica::ColumnTypes &argTypes,
                              Vertica::ColumnTypes &returnType)
    {
        argTypes.addInt();
        argTypes.addInt();
        returnType.addInt();
    }
    
public: 
    Add2intsImmutableFactory() {vol = IMMUTABLE;}
};
RegisterFactory(Add2intsImmutableFactory);

Java Example

The following example demonstrates setting the Add2IntsFactory's vol field to IMMUTABLE to tell Vertica it can cache the arguments and return value.

public class Add2IntsFactory extends ScalarFunctionFactory {

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

    @Override
    public ScalarFunction createScalarFunction(ServerInterface srvInterface){ 
        return new Add2Ints();
    }
    
    // Class constructor
    public Add2IntsFactory() {
        // Tell Vertica that the same set of arguments will always result in the 
        // same return value.
        vol = volatility.IMMUTABLE;
    }
}

Null Input Behavior

To indicate how your function reacts to NULL input, set the strictness field to one of the following values.

Value Description

CALLED_ON_NULL_INPUT

The function must be called, even if one or more arguments are NULL.

RETURN_NULL_ON_NULL_INPUT

The function always returns a NULL value if any of its arguments are NULL.

STRICT

A synonym for RETURN_NULL_ON_NULL_INPUT

DEFAULT_STRICTNESS

The default strictness setting. This is the same as CALLED_ON_NULL_INPUT.

C++ Example

The following example demonstrates setting the null behavior of Add2ints so Vertica does not call the function with NULL values.

class Add2intsNullOnNullInputFactory : public Vertica::ScalarFunctionFactory
{
    virtual Vertica::ScalarFunction *createScalarFunction(Vertica::ServerInterface &srvInterface)
    { return vt_createFuncObj(srvInterface.allocator, Add2ints); }
    virtual void getPrototype(Vertica::ServerInterface &srvInterface,
                              Vertica::ColumnTypes &argTypes,
                              Vertica::ColumnTypes &returnType)
    {
        argTypes.addInt();
        argTypes.addInt();
        returnType.addInt();
    }
    
public: 
    Add2intsNullOnNullInputFactory() {strict = RETURN_NULL_ON_NULL_INPUT;}
};
RegisterFactory(Add2intsNullOnNullInputFactory);

Java Example

The following example demonstrates setting the strictness setting of the Add2Ints example to STRICT. This means that if either of the two values to be added is NULL, Vertica can set the return value to NULL without having to call the Add2Ints function.

public class Add2IntsFactory extends ScalarFunctionFactory {

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

    @Override
    public ScalarFunction createScalarFunction(ServerInterface srvInterface){ 
        return new Add2Ints();
    }
    
    public Add2IntsFactory() {
        // Tell Vertica that any NULL arguments results in a NULL return value
        strict = strictness.RETURN_NULL_ON_NULL_INPUT;
    }
}