8.5. Invoking User-Defined Functions in SQL Statements

Documentation

VoltDB Home » Documentation » Guide to Performance and Customization

8.5. Invoking User-Defined Functions in SQL Statements

Once the class is loaded and the function declared, you can include the user-defined function in SQL queries and data manipulation (DML) statements just as you normally use built-in functions in SQL. For example, if a stored procedure needs to convert an entry in miles to the equivalent measurement in kilometers, the stored procedure definition might look like the following:

CREATE PROCEDURE Expense_in_miles AS
    INSERT INTO Expense_item (item_name, distance, destination)
    VALUES (?, US2METRIC(?,'MILES'), ?);

Similarly, if you want a procedure to find the longest word in each product description for a specific category, the stored procedure definition might look like this:

CREATE PROCEDURE big_words AS
    SELECT product_name, longest_word(description) FROM products
      WHERE category=? GROUP BY product_name;

Note that user-defined functions can be used in queries and DML but cannot be used in data definition statements, such as CREATE statements that define indexes, tables, or views.