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. 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.

The following features are MonetDB specific:

  • CREATE FILTER FUNCTION is a MonetDB SQL language extension.
  • CREATE AGGREGATE is a MonetDB SQL language extension.
  • CREATE WINDOW is a MonetDB SQL language extension.
  • CREATE ... EXTERNAL ...
  • CREATE ... LANGUAGE ...

Associated system table: sys.functions

Examples

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

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

CREATE OR REPLACE FUNCTION pcre_match(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."match";
CREATE OR REPLACE FUNCTION pcre_imatch(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."imatch";
CREATE OR REPLACE FUNCTION pcre_replace(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace";
CREATE OR REPLACE FUNCTION pcre_replacefirst(s string, pattern string, repl string, flags string)
 RETURNS string EXTERNAL NAME pcre."replace_first";
CREATE FUNCTION bbp ()
RETURNS TABLE (id int, name string, htype string,
    string, count BIGINT, refcnt int, lrefcnt int,
    location string, heat int, dirty string, status string,
    kind string)
EXTERNAL NAME sql.bbp;

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

FUNCTION bbp():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 bbp;

Note: 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.

CREATE FUNCTION heapspace(tpe string, i bigint, w int) returns bigint
begin
 if tpe <> 'varchar' and tpe <> 'clob' then return 0;
 end if;
 return 10240 + i * w;
end;

Two useful Microsoft functions supported are 'STUFF' and 'ROUND'.

CREATE OR REPLACE FUNCTION MS_ROUND(num float, precision int, truncat int)
RETURNS float
BEGIN
   IF (truncat = 0)
   THEN RETURN ROUND(num, precision);
   ELSE RETURN TRUNCATE(num, precision);
   END IF;
END;

SELECT MS_ROUND(10.0, 1, 0);


CREATE FUNCTION MS_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;