Function Definitions

The SQL standard allows to create user defined SQL functions. MonetDB supports this feature. MonetDB also allows you to create your own aggregate, window and filter functions. The body of the function or aggregate can also be coded in other languages than PL/SQL such as Python, C, C++ or R provided this functionality has been enabled at MonetDB server startup. Besides returning a scalar value (type = 1), functions can also be defined to return a Table as data type (type = 5). To allow other users to invoke a user-defined function, you must grant the other users (or PUBLIC) EXECUTE privilege for the function.

The syntax to create a function is:

func_def:
     CREATE [OR REPLACE] FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type routine_body
   | CREATE [OR REPLACE] FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE [OR REPLACE] FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type LANGUAGE language_keyword '{' function_impl_code '}'
   | CREATE [OR REPLACE] FILTER FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE [OR REPLACE] AGGREGATE [FUNCTION] qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE [OR REPLACE] AGGREGATE [FUNCTION] qname '(' [ paramlist ] ')' RETURNS func_data_type LANGUAGE language_keyword '{' function_impl_code '}'
   | CREATE [OR REPLACE] WINDOW [FUNCTION] qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name

func_data_type:
      TABLE '(' column data_type ',' ... ')'
    | data_type

language_keyword:
    C | CPP | R | PYTHON | PYTHON_MAP | PYTHON3 | PYTHON3_MAP

External functions provide a convenient bridge between SQL and MAL. This way also a bridge can be established with dynamically loaded functions written in C. Any SQL function signature can be bound to MAL function or command.

Associated system table: sys.functions

Examples

CREATE OR REPLACE FUNCTION MY_ROUND(num double, precision int, truncat int)
RETURNS double
BEGIN
   IF (truncat = 0)
   THEN RETURN round(num, precision);
   ELSE RETURN sys.ms_trunc(num, precision);
   END IF;
END;

SELECT MY_ROUND(1.2345, 3, 0);
SELECT MY_ROUND(1.2345, 3, 1);


CREATE FUNCTION MY_STUFF(s1 varchar(32), st int, len int, s3 varchar(32) )
RETURNS varchar(32)
BEGIN
   DECLARE res varchar(32), aux varchar(32);
   DECLARE ofset int;
   SET ofset = 0;
   SET res = SUBSTRING(s1,ofset,st-1);
   SET res = res || s3;
   SET ofset = LENGTH(s1)-len;
   SET aux = SUBSTRING(s1,ofset, len);
   SET res = res || aux;
   RETURN res;
END;

Some examples using external name binding

CREATE FUNCTION isa_uuid(s string) RETURNS boolean EXTERNAL NAME uuid."isaUUID";

CREATE FUNCTION pcre_match(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."match";
CREATE FUNCTION pcre_imatch(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."imatch";
CREATE FUNCTION pcre_replace(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace";
CREATE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace_first";

The example below illustrates a binding to a function that produces a tabular overview of the BAT catalog.

CREATE FUNCTION getbbp ()
RETURNS TABLE (id int, name string, ttype string,
    count BIGINT, refcnt int, lrefcnt int,
    location string, heat int, dirty string,
    status string, kind string)
EXTERNAL NAME bbp.get;

A table returning function should be implemented as MAL function that returns a series of synchronized BATs.

FUNCTION getbbp():bat[:str,:bat];
   b := bat.new(:str,:bat,12);
   ns := bbp.getNames();
   ri := algebra.markT(ns, 0:oid);
   ...
   kind := algebra.markH(ikind, 0:oid);
   bat.insert(b, "kind", kind);
   RETURN b;
END getbbp;