AppLib MAKE SCRIPT UDF

Declaration

-- procedure to create other execute_function-style methods in applib
create or replace procedure applib.make_script_udf(func_name varchar(255),
  col_names varchar(65535))
language java
parameter style java
modifies sql data
external name 'applib.applibJar:org.eigenbase.applib.script.MakeScriptFuncUdp.execute';

Syntax

CALL APPLIB.MAKE_SCRIPT_UDF(FUNCNAMESTR, CommaSeparatedColumnNamesStr);

Purpose

NOTICE: This procedure is not yet implemented and cannot currently benefit you.

This procedure is meant to ease the creation of multi-argument UDFs that call multi-argument functions in a script. Unfortunately it is not currently possible for LucidDB to call the right variadic method.

Input

• FUNC_NAME: UDF function for future calling. It is created inside APPLIB and uppercased by default, but you can override this behavior by specifying the ["catalog".]"schema"."func" form of a name.
• COL_NAMES: Comma-separated list of arguments to be passed to the script function.

Example

We'll illustrate how we can use this procedure to create our own version of the UDF shown in LucidDbUdfJavaHowto using JavaScript. First, the script:

function execute(input, pattern) {
  return new RegExp(pattern).test(input)
}

Now we'll create a UDF that can talk to this function:

CALL applib.make_script_udf('pattern_match', 'input,output');

Next, the test data:

create schema regex;
create table regex.countries(name varchar(128));
insert into regex.countries
values
    ('Uruguay'),
    ('Paraguay'),
    ('Chile'),
    ('Argentina'),
    ('Venezuela');

Now run it: (since this example isn't using semicolons or single-quotes in the script, it's evaluated as a string, though you should almost always use files for code you care about)

select * from countries where cast(
  applib.pattern_match('js',
    'function execute(input, pattern) {
      return new RegExp(pattern).test(input)
    }',
    'execute',
    name,
    '(A|U).*')
  AS boolean);