Functions, Arguments, and Types

The functions (including procedures, aggregates, window functions, filter functions and loader functions) are all collected in the tables below. It provides an roadmap to check availability of operations supported.

Functions can be overloaded, so the same function name can exist multiple times in sys.functions but each will have different arguments, so different function signatures.

sys.functions

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for the function/procedure/aggregate/window.
"name"VARCHARThe SQL name.
"func"VARCHARMAL function identifier or SQL function definition.
"mod"VARCHARMAL function module identifier.
"language"INTEGERsys.function_languages.language_idThe programming language id code in which the function is defined, see table sys.function_languages.
"type"INTEGERsys.function_types.function_type_idType of function, see table sys.function_types.
"side_effect"BOOLEANIt does (not) lead to updates.
"varres"BOOLEANDoes the function have variable output result?
"vararg"BOOLEANCan the function be called with a variable number of input arguments?
"schema_id"INTEGERsys.schemas.idThe schema the function belongs to.
"system"BOOLEANIs it a preloaded MonetDB system function/procedure/aggregate/window.
"semantics"BOOLEANWhen set to true, function incorporates some kind of null semantics
"order_specification"TINYINTWhether an aggregate function allows an ORDER BY clause. 0=Not Allowed, 1=Optional On Aggregates, 2=Required On Aggregates.

sys.function_languages

nametypereferencesdescription
"language_id"SMALLINTThe unique internal identifier of a function programming language.
"language_name"VARCHARA description of the programming language.
"language_keyword"VARCHARThe SQL language keyword recognised by the SQL parser.
language_idlanguage_namelanguage_keyword
0Internal C
1MAL
2SQL
3RR
4CC
6PythonPYTHON
10Python3PYTHON3
12C++CPP

sys.function_types

nametypereferencesdescription
"function_type_id"SMALLINTThe unique internal identifier of a function type.
"function_type_name"VARCHARA description of the function type.
"function_type_keyword"VARCHARThe SQL function type keyword recognised by the SQL parser.
function_type_idfunction_type_namefunction_type_keyword
1Scalar functionFUNCTION
2ProcedurePROCEDURE
3Aggregate functionAGGREGATE
4Filter functionFILTER FUNCTION
5Function returning a tableFUNCTION
6Analytic functionWINDOW
7Loader functionLOADER

sys.args

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for function argument.
"func_id"INTEGERsys.functions.idThe corresponding function.
"name"VARCHARThe SQL name.
"type"VARCHARsys.types.sqlnameThe SQL type name.
"type_digits"INTEGERThe number of digits for number types.
"type_scale"INTEGERThe precision after decimal point.
"inout"TINYINTWhether the argument is usable for input (inout = 1) or output (inout = 0).
"number"INTEGERThe function argument position ranging from 1 to n for functions and from 0 to n-1 for procedures (sys.functions.type = 2). For functions a number 0 represents the return arg type.

sys.types

nametypereferencesdescription
"id"INTEGERThe unique internal identifier for the data type.
"systemname"VARCHARMAL data type name.
"sqlname"VARCHARSQL data type name.
"digits"INTEGERThe number of digits (in the specified radix) for numerical, floating point, time, timestamp and interval types.
"scale"INTEGERType of scale. 0=not applicable, 1=decimal, 2=exponential (floating point).
"radix"INTEGERRadix for the number of digits value: 2 (= binary) or 10 (= decimal).
"eclass"INTEGERThe internal storage class equivalence identifier.
"schema_id"INTEGERsys.schemas.idThe schema identifier.