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:

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 qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | CREATE [OR REPLACE] AGGREGATE qname '(' [ paramlist ] ')' RETURNS func_data_type LANGUAGE language_keyword '{' function_impl_code '}'

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

language_keyword:
    C | CPP | R | PYTHON | PYTHON_MAP | PYTHON2 | PYTHON2_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 example below illustrates a binding to the a function that produces a tabular overview of the BAT catalog.

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;

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

The SQL definition for MS_STUFF is:

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;

 

Note: CREATE FILTER FUNCTION is a MonetDB language extension. The SQL standard does not provide for user-defined filter functions such as my_like ... .

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

Note: CREATE ... EXTERNAL ... and CREATE ... LANGUAGE ... are MonetDB language extensions.