7.4. Writing Stored Procedures Inline Using Groovy


VoltDB Home » Documentation » Using VoltDB

7.4. Writing Stored Procedures Inline Using Groovy


Use of embedded Groovy stored procedures is supported for compiled catalogs only. See the appendix on Using Application Catalogs in the VoltDB Administrator's Guide for more information.

Writing stored procedures as separate Java classes is good practice; Java is a structured language that encourages good programming habits and helps modularize your code base. However, sometimes — especially when prototyping — you just want to do something quickly and keep everything in one place.

You can write simple stored procedures directly in the schema by embedding the procedure code using the Groovy programming language (http:// groovy.codehaus.org/). Groovy is an object-oriented language that dynamically compiles to Java Virtual Machine (JVM) byte code. Groovy is not as strict as Java and promotes simpler coding through implicit typing and other shortcuts. It is important to note that Groovy is an interpreted language. It is very useful for quick coding and prototyping. However, Groovy procedures do not perform as well as the equivalent compiled Java classes. For optimal performance, Java stored procedures are recommended.

You embed a Groovy stored procedure in the schema by including the code in the CREATE PROCEDURE AS statement, enclosed by a special marker — three pound signs (###) — before and after the code. For example, the following DDL uses the CREATE PROCEDURE AS statement to implement the Insert stored procedure from the Hello World tutorial (doc/tutorials/helloworld) using Groovy:

CREATE PROCEDURE Insert AS ###                          1
   sql = new SQLStmt(                                   2
   transactOn = { String language,                      3
                  String hello,
                  String world ->
      voltQueueSQL(sql, hello, world, language)
### LANGUAGE GROOVY;                                    4

Some important things to note when using embedded Groovy stored procedures:


Begin with three pound signs (###) before the code. The definitions for VoltTypes, VoltProcedure, and VoltAbortException are automatically included and can be used without explicit import statements.


As with Java stored procedures, you must declare all SQL queries as SQLStmt objects at the beginning of the Groovy procedure.


You must also define a closure called transactOn, which is invoked the same way the run() method is invoked in a Java stored procedure. This closure performs the actual work of the procedure and can accept any arguments that the Java run method can accept. It can also return a VoltTable, an array of VoltTable, or a long value.


End the DDL statement with three pound signs (###) after the Groovy code.

In addition, VoltDB provides special wrappers, tuplerator() and buildTable(), that help you access VoltTable results and construct VoltTable structures from scratch. For example, the following code fragment shows the ContestantWinningStates() stored procedure from the Voter sample application (examples/voter) written in Groovy:

transactOn = { int contestantNumber, int max ->

    results = []
    state = ""

    tuplerator(voltExecuteSQL()[0]).eachRow {
        isWinning = state != it[1]
        state = it[1]

        if (isWinning && it[0] == contestantNumber) {
            results << [state: state, votes: it[2]]
    if (max > results.size) max = results.size
    buildTable(state:STRING, num_votes:BIGINT) {
        results.sort { a,b -> b.votes - a.votes }[0..<max].each {
            row it.state, it.votes