AppLib EXECUTE FUNCTION

Declarations

-- executes parameter-less function "func_name" with String return type.
-- (There are also two variants of this that allow passing one argument or two.)
create or replace function applib.execute_function(
    engine_name varchar(255), 
    script varchar(65535),
    func_name varchar(255))
returns varchar(65535)
language java
specific execute_function
parameter style system defined java
modifies sql data
external name 'applib.applibJar:org.eigenbase.applib.script.ExecuteScriptUdr.executeUdf';

-- executes default function "execute"
create or replace function applib.execute_function(
    engine_name varchar(255), 
    script varchar(65535))
returns varchar(65535)
language java
specific execute_function2
parameter style system defined java
modifies sql data
external name 'applib.applibJar:org.eigenbase.applib.script.ExecuteScriptUdr.executeUdf';

Syntax

VALUES(APPLIB.EXECUTE_FUNCTION(engineStr, scriptStrOrFile[, FunctionNameStr[, arg1[, arg2] ] ]));

Purpose

This function provides a way to call predefined functions (that take no arguments, one argument, or two arguments), defaulting to trying to run the "execute" function if you don't provide a name in the 0-argument case. This can save the user some work of making sure a startup function (such as main()) is called when trying to run a script. It also provides a way to get return values back to LucidDB as Strings which can be casted to other types.

When you want to have a similarly used UDF that might take one or more input arguments, see AppLib_MAKE_SCRIPT_UDF which provides a way to easily create APPLIB functions to call.

Input

• ENGINE_NAME: Script engine to use.
• SCRIPT: Script to run.
• FUNC_NAME: Global script function to call, default is "execute".
• ARG: Optional first param to pass to the function.
• ARG2: Optional second param to pass to the function.

Example

0: jdbc:luciddb:> values(applib.execute_function('js', 'function execute() {
. . . . . . . . >     return "I love cookies."
. . . . . . . . >   }'));
+------------------+
|      EXPR$0      |
+------------------+
| I love cookies.  |
+------------------+
1 row selected (0.036 seconds)
0: jdbc:luciddb:> values(applib.execute_function('js', 'function foo() {'
. . . . . . . . > '    var fn = function() {'
. . . . . . . . > '      return "I really love cookies with functions inside.";'
. . . . . . . . > '    };'
. . . . . . . . > '    return fn();'
. . . . . . . . > '  }', 'foo'));
+-----------------------------------------------+
|                    EXPR$0                     |
+-----------------------------------------------+
| I really love cookies with functions inside.  |
+-----------------------------------------------+
1 row selected (0.347 seconds)
0: jdbc:luciddb:>

Interestingly, bad things will happen if you have a single pair of single quotes rather than quoting line-by-line like in the second example if you intend to use semicolons. (Try putting a semicolon after the first example's return statement to see the error.) This seems to be a problem with the sqlLine program, and should not be a problem in other environments. Therefore it is strongly recommended to use files rather than scripting directly in sqlLine.

Passing Parameters Example

Included in applib are two additional execute_function definitions that let you pass one argument or two to a script. Currently in order to pass a different number of arguments, you have to make a new function in the Java class compiled with applib. Or you can fake it with execute_transform.

We'll illustrate how we can use the two-argument form 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)
}

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 regex.countries where cast(
  applib.execute_function('js',
    'function execute(input, pattern) {
      return new RegExp(pattern).test(input)
    }',
    'execute',
    name,
    '(A|U).*')
  AS boolean);

You should get Argentina and Uruguay back out.