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
   | CREATE [OR REPLACE] LOADER [FUNCTION] qname '(' [ paramlist ] ')' LANGUAGE PYTHON '{' function_impl_code '}'

func_data_type:
      data_type
    | TABLE '(' columnname data_type [ ',' ... ] ')'

language_keyword:
    C | CPP | PYTHON | PYTHON3 | R

routine_body:
      procedure_statement
    | BEGIN procedure_statement_list procedure_statement ';' END
    | BEGIN ATOMIC procedure_statement_list procedure_statement ';' END

procedure_statement:
      update_statement
    | select_statement_single_row
    | schema
    | grant
    | revoke
    | create_statement
    | drop_statement
    | alter_statement
    | declare_statement
    | set_statement
    | control_statement
    | transaction_statement

update_statement:
      delete_stmt
    | truncate_stmt
    | insert_stmt
    | update_stmt
    | merge_stmt
    | copyfrom_stmt
    | copyto_stmt

declare_statement:
      DECLARE variable_list
    | DECLARE table_def

set_statement:
      SET variable_ref '=' search_condition
    | SET variable_ref_commalist_parens '=' subquery
    | SET SESSION AUTHORIZATION opt_equal ident
    | SET session_schema opt_equal ident
    | SET session_user opt_equal ident
    | SET session_role opt_equal ident
    | SET session_timezone opt_equal LOCAL
    | SET session_timezone opt_equal literal

control_statement:
      call_procedure_statement
    | call_statement
    | while_statement
    | if_statement
    | case_statement
    | return_statement

transaction_stmt:
      { START | BEGIN } TRANSACTION transaction_mode_list
    | COMMIT opt_work opt_chain
    | ROLLBACK opt_work opt_chain opt_to_savepoint
    | SAVEPOINT ident
    | RELEASE SAVEPOINT ident
    | SET LOCAL TRANSACTION transaction_mode_list
    | SET TRANSACTION transaction_mode_list

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.

See also system tables/views: sys.functions where type <> 2, sys.args,
information_schema.routines where routine_type <> 'PROCEDURE',
information_schema.parameters where function_type <> 2.

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;