Implementing Scalar Functions

If there are inaccuracies discovered with this documentation, please submit a GitHub issue.

Introduction

The PartiQL Library allows for the integration of custom scalar functions for planning and execution of PartiQL queries.

Who is this for?

This usage guide is aimed at developers who want to implement and provide scalar functions to the PartiQL planner and/or compiler.

Examples of custom scalar functions include:

  1. A function to encrypt information (e.g. ENCRYPT('my_password'))

  2. An overloaded function (e.g. FOO(INT) → INT, FOO(SMALLINT) → SMALLINT, FOO(DECIMAL(1, 2)) → DECIMAL(1, 2))

  3. An overridden function (e.g. ABS(INT) → INT)

Prerequisites

To get the APIs discussed in this usage guide, please take a dependency on the SPI package.

build.gradle.kts
dependencies {
    implementation("org.partiql:partiql-spi:1.+")
}

This usage guide references many aspects of the Implementing a Catalog usage guide. Please read the usage guide before continuing.

Implementation

Hello, __! (Simple Example)

In this example, we will create a scalar function that takes in a person’s name and outputs a greeting for that person. For example, with the "Jackson" input, the function will return "Hello, Jackson!".

HelloFriend.java
import org.partiql.spi.function.FnOverload;
import org.partiql.spi.types.PType;
import org.partiql.spi.value.Datum;

public class HelloFriend {
    /**
     * @return a function with a single implementation.
     */
    public static FnOverload getImpl() {
        return new FnOverload.Builder("hello_friend")
                .addParameter(PType.string())
                .isNullCall(true)
                .isMissingCall(true)
                .returns(PType.string())
                .body((args) -> {
                    Datum arg = args[0];
                    String argStr = arg.getString(); // Notice that we can safely get the string here.
                    String returnStr = "Hello, " + argStr + "!";
                    return Datum.string(returnStr);
                })
                .build();
    }
}

In the above code snippet, notice that we:

  1. Unsafely grabbed the first argument at index 0.

  2. Did not check whether the argument was null or missing.

  3. Unsafely retrieved the underlying string of the argument.

If you have read the Using Datum usage guide, your brain is likely raising some red flags. However, in this case, you actually should not be checking the datum’s properties. You are empowered to "unsafely" ignore the datum’s properties by specifying an exact type with addParameter() and by specifying what the compiler should do when the argument is null or missing (respectively, isNullCall() and isMissingCall()). The planner and compiler will provide sufficient compile-time and runtime checks before invoking your function.

Overloading Function (Complex Example)

Right now, the FnOverload API is implemented internally to overload some of the more complex binary operators that lead to a cartesian product of input types for a given function. This is leveraged for performance reasons, and direct implementation of FnOverload is not recommended.

If you’d like to overload functions in the simplest way possible, we recommend that you use FnOverload.Builder multiple times and pass all FnOverload(s) to your catalog. With the IonCatalog from Implementing a Catalog, an example of providing multiple overloads can be seen below:

IonCatalog.java
    @NotNull
    private final Map<String, FnOverload> functions = new HashMap<String, List<FnOverload>>() {{
        // This is an example of providing multiple FnOverloads
        put("is_directory", new ArrayList<FnOverload>() {{
            add(is_directory_1);
            add(is_directory_2);
            add(is_directory_3);
        }});
    }};

    @NotNull
    @Override
    public Collection<FnOverload> getFunctions(@NotNull Session session, @NotNull String name) {
        return functions[name];
    }

Integration

Please see the Implementing a Catalog usage guide to integrate your custom function into a catalog.