Skip to main content

Functions

The create function statement defines a new function stored in the database. Function names are not case sensitive and are internally stored upper-cased.

Example:

create function absdiff(Number x, Number y) -> Number
  as abs(x-y)
absdiff(3,5)

The result of a create function call is an object of type Function.

Function signatures​

A function consists of two parts, the signature and the body:

The function signature is a human readable string containing the types and names of the arguments and result of a function. For example, absdiff(Number x, Number y)->Number.

You can call the system function signature(Charstring fn)->Bag of Charstring to get the signatures of all functions named fn.

Examples:

signature("absdiff")
signature("mod")
signature("range")

There is only one function named mod, while the function range is an overloaded function having three different resolvents with different definitions depending on their argument types.

The signature of a Boolean function, i.e. a function whose result type is `Boolean, has no result type.

Example:

signature("=")

You can also specify the function's name in signature(fn) as a string pattern.

Examples:

signature("atan%")
signature("atan_")

Function bodies​

The function body specifies how to compute the result of a function for given argument values. For example, the body of the function plus(Number x,Number y)->Number r specifies how to compute the result r by adding the arguments x and y.

Example:

create function add1(Integer i) -> Integer
  as i + 1

The signature of add1 is add1(Integer i)->Integer and the body is i+1.

In many cases the system can infer the inverse of a function call in a query for given results. For example, the body of plus(Number x,Number y)->Number r also specifies how to compute y when x and r are known; the system knows that - is the inverse of +.

Example: The following set query calls the inverse of +, i.e. - by analyzing the body of add1 when i is unknwówn in the expression add1(i)=5.

select i
  from Integer i
 where add1(i) = 5

The function body is normally non-procedural (or declarative), i.e. a function only computes result values for given arguments (and vice versa when possible) and does not have any side effects. The exception is procedural functions defined through OSQL statements having side effects.

In general, depending on their bodies, functions can be one of the following kinds:

  • Stored functions are tabulated functions whose values for given parameters can be updated.

  • Derived functions are defined by a single expression or query that returns the result of a function call for given parameters.

  • Procedural functions are defined using procedural OSQL statements that can have side effects changing the state of the local database or the environment. Procedural functions make OSQL computationally complete.

  • Foreign functions are defined in an external programming language. Foreign functions can be defined in the programming languages Lisp, Java, Python, C, or C++.

  • Overloaded functions have different implementations depending on the argument types in a function call.

Stored functions​

Stored functions are functions whose mappings from arguments to results, their extents, are stored in the local database of a peer. They are an alternative to SQL tables to represent simple properties as mappings from arguments to values.

A stored function is defined by the function body as stored.

Example:

create function income(Charstring person) -> Real
  as stored

You can update the value of a stored function for given arguments using a variant of the set statement. You are then updating the extent of the function and can query it as a functional expression.

Example:

set income('Tore') = 1000;
set income('Ulla') = 2000
income('Tore')

The set statement allows to change stored function values.

Example:

set income('Tore') = 1500
income('Tore')

The result of a stored function may be a single value as for income. It may also be a bag.

Example: 

create function parents(Charstring child) -> Bag of Charstring
  as stored
set parents('Tore') = (values 'Ulla', 'Olof');
set parents('Ulla') = 'Karl'
parents('Tore')

You can add elements to the result of functions returning bags with the add statement.

Example:

add parents('Ulla') = 'Marie'
parents('Ulla')

You can remove all stored values for given arguments by setting them to null.

Example:

set parents('Tore') = null
parents('Tore')

The function extent(Function f)->Bag of Vector returns rows in function f as vectors.

Example:

```osql wasm extent(thefunction('parents')) ```

Here the function thefunction(Charstring fn)->Function returns the function named fn. It fails if there is no such function.

Function calls​

In a function call, the types of the actual parameters must be the same as, or sub-types of, the types of the corresponding formal parameters.

Example: This function call raises an exception:

range('10')

If a function is applied on the result of a function returning a bag of values, the outer function is applied on each element of that bag, the bag is flattened. This is called Daplex semantics.

Example: The following query returns the grandparents of 'Tore'.

parents(parents('Tore'))

In the query the function parents(p) returns a bag of two strings, for which the function parents() is applied again on each element.

Infix operators​

The built-in function plus() has a corresponding infix operator + where x+y is equivalent to plus(x,y). Analogously there are infix operators - for minus(), * for times(), and / for div(), etc.

Example:

(sin(3.14)+sin(6.28)) * 0.5

is equivalent to:

times(plus(sin(3.14),sin(6.28)),0.5)

The infix operator || concatenates strings.

Example:

upper("Hello World") || '!'
Note

For legacy reasons the infix + operator can be used for string concatenation as well, but this is discouraged as ambiguities may occur since + is also defined for many other data types than strings.

The in operator​

If a function returns a collection the elements of the collection can extracted by using the infix in operator.

Example: The following query returns the grandparents of 'Tore' without using Daplex semantics.

select gp
  from Charstring gp, Charstring p
 where p in parents('Tore')
   and gp in parents(p)

The in operator actually converts the elements of the collection into a bag.

Casting​

The cast operator can be used for explicitly converting between different types.

Example:

cast(1.1 as Integer)

Tuple valued functions​

A function my return not only singleton values, but also tuples of two or more values.

Example:

divide(5,3)

In this case the function divide(Integer i,Integer j)->(Integer q,Integer r) is a tuple valued function that returns both the quotient q and the remainder r of dividing i with j. The function returns the result tuple (q,r) for given argument tuple (i,j).

To bind the results of tuple valued functions use tuples.

Example: The following query uses the tuple (d,rem) to compute the remainder while ignoring the quotient:

select rem
  from Integer d, Integer rem
 where (d,rem) = divide(5,3)

Tuples can also be used to assign separate variables to the result of a function returning a tuple.

Example:

set (:d,:r)=divide(5,3)
:d + :r

Derived functions​

A derived function is defined by a single expression or query.

Examples:

create function degrees(Real radians)->Real
  as radians*180/pi()
degrees(pi())

Derived functions usually return bags of values.

Examples:

create function square_roots(Real x)->Bag of Real
  as select (values r,-r)
       from Real r
      where r = sqrt(x)
square_roots(2)

The query in a derived function may reference both local variables and table columns.

Example: Let's create a new table Person.

drop table Person;
create table Person(
       id Integer,
       name Charstring,
       income Real,
       unique(id));
insert into Person values
(1,'Tore',1000.0),
(2,'Kalle',2000.0)

The derived function rich_persons(l) selects the names of persons earning more than l from table Person.

create function rich_persons(Real l) -> Bag of Charstring
  as select name
       from Person
      where income > l
rich_persons(1000)

To disambiguate name collisions, use SQL's dot notation to access table column names in queries.

Example:

create function person_name(Integer id) -> Charstring
  as select name
       from Person p
      where p.id = id
person_name(1)

Derived Boolean functions return true when a condition is fulfilled.

Example:

create function positive(Number n) -> Boolean
  as n > 0
positive(2)

Aggregate functions​

Aggregate functions compute a single result from the elements in a collection not using Daplex sematics.

Examples:

avg(values 1,2,3)
avg([1,2,3])
avg(array([1,2,3]))

The argument of an aggregate function is a collection and the result is a single value.

Example:

signature("avg")

You can define your own aggregate function over bags by declaring its argument Bag of ...

Example:

create function myavg(Bag of Number b) -> Number
  as sum(b)/count(b)
myavg(values 1,2,3)

Aggregate functions over bags can be used in group by.

Overloaded functions​

Functions may be overloaded, i.e. functions having the same generic name may be defined differently for different argument types. This allows to define functions applicable on objects of several different argument types. Each specific implementation of an overloaded function is called a resolvent.

Example: The following two function definitions create two resolvents of the overloaded Boolean function less():

create function less(Number i, Number j)->Boolean
  as i < j;
create function less(Charstring s,Charstring t)->Boolean
  as s < t

You can inspect what resolvents are defined for a given generic function name g by calling signature(g).

Example:

signature('less')

You can also retrieve the object representing the resolvents for a given generic function by calling resolvents(g).

Example:

resolvents('less')

Internally the system stores the resolvents under different mangled function names. The internal name of a function f is obtained by calling name(f).

Example:

name(resolvents('less'))

The mangled name of a resolvent is obtained by concatenating the type names of its arguments with the name of the overloaded function followed by the symbol -> and the type of the result.

The query processor resolves the correct resolvent to apply based on the types of the arguments in a function call. The type of the result is not considered. If no matching resolvent is found, an error is generated.

When overloaded function names are encountered in function bodies, the compiler will try to use local variable declarations to choose the correct resolvent at compile time. This is called early binding.

Example:

create function income(Charstring person) -> Real
  as stored;
set income('Tore') = 1000;
set income('Kalle') = 2000
create function richer(Charstring p) -> Bag of Charstring
  as select q
       from Charstring q
      where less(income(p),income(q))
richer('Tore')

Here the system will choose the resolvent with signature less(Number i,Number j) since the stored function income returns objects of type Real. The other resolvent less(Charstring s,Charstring t) is not applicable since it requires strings as arguments.

Higher-order functions​

OSQL functions are internally represented as any other objects and stored in the database. Object representing functions can be used in functions and queries too. An object representing a function is called a functional and is represented as instances of type Function. Higher-order functions are functions that take functionals as arguments or results.

Functionals​

The functional constant notation #'fn' denotes the unique resolvent for the function named fn.

Example: this expression denotes the single resolvent for the generic function named sin.

#'sin'

An error is raised if the function name specified as a functional constant does not uniquely identify the function. This happens if no function named fn exists or if it is the generic name of an overloaded function.

Example:

#'not-a-function'

If `he functional constant #'range'is illegal, sincerange` is overloaded. For overloaded functions the name of a resolvent has to be used instead.

#'range'

To get the resolvent for range(Integer u)->Bag of Integer use its mangled name:

#'integer.range->integer'

The high-order function thefunction(Charstring fn)->Function returns the functional having the name fn. It fails if no such function exists.

Example: The following function call returns the object representing the generic function range.

thefunction('range')

Dynamic function calls​

The variadic function call(Function f, Object a1,Object a2,..)->Object calls the functional f with arguments a1,a2,.... The difference to a regular function call is that it allows the called function to be a variable bound to functional or a functional constant. The name of the function need not be known beforehand.

Example: The following function applies on a bag b an arbitrary aggregate function aggfn that returns a number.

create function bag_agg(Function aggfn, Bag b) -> Number
  as call(aggfn, b)

Let's test it:

bag_agg(thefunction('avg'), bag(1,2,3))

You can use generic functions when applying non-unique resolvents, in which case call will choose the correct resolvent based on the types in the argument vector.

Example:

call(thefunction('+'),2,3.5)

The very general higher-order system function apply(Function fno, Vector argl)->Bag of Vector calls the function fno with the vector argl as argument list. It returns a bag of result tuples represented as vectors.

Example:

apply(#'divide',[5,3])

Notice how apply() represents both argument and result tuples as vectors. In the example the result tuple is represented by the vector [1,2].

note

It is better and much more efficient to use call rather than apply when the number of arguments in the call are known beforehand. The query above could also be written as a variadic call that returns a tuple:

call(#'divide',5,3)

Visit High order for a list of higher-order functions.

Deleting functions​

Functions are deleted with the delete function statement.

Example:

delete function less

Deleting a function also deletes all functions calling the deleted function, so the statement will delete both less and richer.

Example: This fails after executing delete function less:

richer('Tore')

The library of basic mathematical functions is documented in Math functions.

The use of aggregate functions in queries is explained in Aggregate functions in queries are treated different from other functions in queries.

The library of system aggregate functions is documented in aggregate functions.

The use of functions taking other functions as arguments is documented in High order functions.

The use of functions with side effects is documented in Procedural and stateful functions.