Skip to main content

Function definitions

The SQL standard allows to create SQL functions. MonetDB supports this feature. The syntax to create a function is:

     CREATE FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type  routine_body
   | CREATE FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE AGGREGATE qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name

      TABLE '(' column data_type ','... ')'
    | data_type type_def: TYPE qname EXTERNAL NAME ident

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 example below illustrates a binding to the a function that produces a tabular overview of the BAT catalog.

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)

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

FUNCTION bbp():bat[:str,:bat];
   b :=,: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;

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

SQL> SELECT MS_ROUND(10.0, 1, 0);

The SQL definition for MS_ROUND is:

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

The SQL definition for MS_STUFF is:

CREATE FUNCTION MS_STUFF( s1 varchar(32), st int, len int, s3 varchar(32))
RETURNS varchar(32)
   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;


Note: CREATE AGGREGATE is a MonetDB language extension. The SQL standard does not provide for user-defined aggregate functions.