CREATE FUNCTION

CREATE FUNCTION

You can create or replace a JavaScript UDF, by specifying the function name, input and output data types. Please check the mapping of data types for input and output.

The following example defines a new function test_add_five_5:

CREATE OR REPLACE FUNCTION test_add_five_5(value float32)
RETURNS float32
LANGUAGE JAVASCRIPT AS $$
  function test_add_five_5(values) {
    for(let i=0;i<values.length;i++) {
      values[i]=values[i]+5;
    }
    return values;
  }
$$;

Note:

• Line 1: the function is to be created with name test_add_five_5, taking a single float32 parameter.
• Line 2: the function is to return a float32
• Line 4: the same function name is defined in the code block. To improve performance, multiple UDF calls will be batched. The values is an array of float
• Line 5 and 6: iterating the input array and add 5 to each value
• Line 8: return an array of new values
• Line 10: close the code block.

info

In Timeplus Enterprise, you can add debug information via console.log(..) in the JavaScript UDF. The logs will be available in the server log files.

CREATE AGGREGATE FUNCTION

Creating a user-defined-aggregation function (UDAF) requires a bit more effort. Please check this documentation for the 3 required and 3 optional functions.

CREATE AGGREGATE FUNCTION test_sec_large(value float32)
RETURNS float32
LANGUAGE JAVASCRIPT AS $$
    {
      initialize: function() {
         this.max = -1.0;
         this.sec = -1.0
      },
      process: function(values) {
        for (let i = 0; i < values.length; i++) {
          if (values[i] > this.max) {
            this.sec = this.max;
            this.max = values[i]
          }
          if (values[i] < this.max && values[i] > this.sec)
            this.sec = values[i];
        }
      },
            finalize: function() {
            return this.sec
            },
            serialize: function() {
            let s = {
            'max': this.max,
            'sec': this.sec
            };
        return JSON.stringify(s)
      },
        deserialize: function(state_str) {
                                           let s = JSON.parse(state_str);
                                           this.max = s['max'];
                                           this.sec = s['sec']
        },
        merge: function(state_str) {
                                     let s = JSON.parse(state_str);
                                     if (s['sec'] >= this.max) {
                                     this.max = s['max'];
                                     this.sec = s['sec']
                                     } else if (s['max'] >= this.max) {
                                     this.sec = this.max;
                                     this.max = s['max']
                                     } else if (s['max'] > this.sec) {
                                     this.sec = s['max']
                                     }
                                     }
        }
$$;