SQL Create Function

The create function statement is used to create a User Defined Function.

The complete syntax is as follows:

 
    CREATE FUNCTION name "(" { arg_name data_type  [ "," ... ] ")"
        RETURNS return_type [VOLATILE]
 
    AS library_path ["," c_function_name ] | 
    IN "'"language_name"'" AS "'"function_body"'"
     
    data_type: 
        BIT | BOOLEAN | CHAR [ "(" size ")" ] | VARCHAR [ "(" size ")" ] | LOGNVARCHAR [ "(" size ")" ] | 
        STRING [ (" width ")" ] | UNICODE[ (" width ")" ]  -- corresponds to eXtremeDB nstring or nchar
        | TINYINT    -- signed<1>
        | SMALLINT -- signed<2>
        | INT [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
        | INTEGER [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB signed<n>
        | UNSIGNED [ "(" n ")" ]      -- where n=1,2,4,8 corresponds to eXtremeDB unsigned<n>
        | BIGINT -- signed<8>
        | FLOAT
        | REAL -- double
        | DOUBLE
        | NUMERIC [ "(" width [ "," precision ]  ")" ]  -- eXtremeDB numeric< width,precision> type
        | DECIMAL [ "(" width [ "," precision ]  ")" ]   -- eXtremeDB numeric< width,precision> type
        | DATE -- eXtremeDB 8-byte datetime type
        | TIME   -- eXtremeDB 8-byte datetime type
        | TIMESTAMP  -- eXtremeDB 8-byte datetime type
        | BIANRY [ "(" size ")" ]  --- array or vector of signed<1>
        | VARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
        | LONGVARBINARY  [ "(" size ")" ]   --- array or vector of signed<1>
        | REFERENCE
        | BLOB
        | ARRAY "(" data_type [ "," length ] ")"
        | SEQUENCE "(" scalar_data_type [ ASC | DESC ] ")"
        | user-defined-type  -- declared by "create domain"
         

Note that all SQL keywords are case insensitive in eXtremeSQL - i.e. "CREATE FUNCTION" and "create function" are equivalent.

Examples

For example, the following SQL statement could be executed for loading the C++ user-defined function load_cme_trades() as SQL function load_trades():

 
    create function load_trades(fname string, book string) returns integer as 'mcocme', 'load_cme_trades';
            
     

Here mcocme refers to the dynamic load file libmcocme.so (or mcocme.dll on Windows systems) that contains the implementation of UDF load_cme_trades.

When executed this statement creates table Functions, if it is not already created, and inserts the row defining this UDF.

To create function defined in scripting language, which can be called stored procedure, the second form of the create statement can be used. For example, a Lua UDF could be defined as follows:

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

Or, a Python UDF could be defined as follows:

 
    create function pyadd(x int, y int) returns int in 'python' as 'return x+y end';
     

The UDFs can then be executed in SQL with a statement like the following:

 
    XSQL>select twice(3), pyadd(5,7);
    #1 #2
    -----------------------------------------
    6 12
 
    Selected records: 1
     

When a Python UDF is first called, it loads the python interpreter, compiles the function code and executes it. A reference to the created interpreter object and compiled code is saved in memory, so this loading process occurs only one time in the life of the application and the UDF is compiled only once.

(Note that, for Windows users, a Python UDF module (DLL) mcopythonapi.dll is a plugin module for the eXtremeSQL engine, and has a link dependency to library mcosql.dll. This means that to use a UDF (Lua as well as Python UDFs) from a user program, it must load eXtremeSQL and the eXtremeDB core runtime dynamically. See mco_core_load() for further details)

Function Caching

When a function call is performed, the SQL engine will store the function result and use this cached value for subsequent calls of this function if the function arguments are the same; i.e. the arguments are the same syntactic construct in the query.

So call will be cached if:

• the argument is a constant expression

or

• the argument is physically the same database value; i.e. it is the same column value in the same row in the same table.

It will not be cached if:

• a constant expression or column appeared more than one time in a query

Practically, only two use cases are known to use the function result caching:

• an evaluation function of a constant expression for many rows in the result
• when joining two tables A and B in a query like "select * from A,B where sin(A.x) > 0;". Here function sin(A.x) will be called only cardinality(A) times, but not cardinality(A) * cardinality(B).

For some functions this caching is not appropriate, e.g. random(). To disable caching for a particular function, the VOLATILE flag can be used.

Example

     
    create function vmtf(name varchar, dept_no unsigned(2), salary unsigned(4)) returns int VOLATILE in 'lua' as '
        require("test_udf")
            return mtf_impl(name, dept_no, salary)
        end
        ';
         

(To remove a function a drop function statement is used.)