Lua User Defined Functions

Lua is the best choice for writing User Defined Functions (UDF's) for eXtremeSQL because they are embedded into the server and execute in the context of the server, making data processing as close as possible to the data. As a UDF is a part of a query, it is performed in the context of a particular connection and transaction. It is possible to get access to the Connection object and perform SQL statements for both data retrieval and data update from the UDF, for making more complex queries.

Value Returning UDF

The following statement creates a simple value returning function, which takes an integer and performs some trivial operation on it:

 
    create function twice(x integer) returns integer in 'lua' as 'return x+x end';
     

To call this function, execute a statement like the following:

 
    XSQL>select twice(2);
     

Result Set Returning UDF

If a table is returned, it can be used in any context in a SQL query where any other table or dataset is used. A result set returning UDF will use some table in the database as a definition for it’s own result set. So it is necessary to first create a table that defines the result set for the UDF. For example:

 
    create table qt1
    (
        dt int,
        sym string,
        putorcall char(1),
        strikePrice real
    );
     

Then we can define a UDF that will return this table. For example:

 
    CREATE FUNCTION q1(syms array(string), d int) returns qt1 in 'lua' as '
        local ret = {
            {dt=101010, sym="AAPL", putorcall="C", strikePrice=10.1},
            {dt=101010, sym="IBM", putorcall="C", strikePrice=40.1}
        }
        return ret
    end';