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.

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

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

• The signature defines the types and names of the arguments and result of the function.

• The function body specifies how to compute the result of a function given a tuple of argument values. For example, the function + computes the result by adding its arguments. The function body is normally non-procedural, i.e. a function only computes result values for given arguments and does not have any side effects. The exception is procedural functions defined through OSQL statements having side effects.

Function signatures

The function signature defines the types of the arguments and results of a function, for example:

mod(Number x,Number y)->Number
range(Integer l,Integer u)->Bag of Integer

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

signature("atan");
signature("range")

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

You can also specify the function's name in signature(fn) as a string pattern where a * matches a sequence of one or more character, while a ? matches a single character. For example:

signature("atan*");

Function bodies

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 that returns the result of the a function call for given parameters.

• Procedural functions are defined using procedural OSQL statements that can have side effects changing the state of the database. 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 tables stored in the local database of a peer. A stored function is defined by the function body as stored, for example:

create type Person;
create function name(Person p) -> Charstring
  as stored;
create function parents(Person p) -> Bag of Person
  as stored;
create function income(Person p) -> Real
  as stored;

For stored functions you may optionally specify parameter names of arguments or results, such as p and c in the example.

The result of a stored function may be a single values as for name and income in the example. The value of a stored function may also be a bag as parents in the example. Stored functions cannot have arguments declared Bag of.

Our example's stored functions can be populated using the create statement:

create Person(name, income, parents) instances
  :p1 ("Maja",    100, null),
  :p2 ("Bill",    200, null),
  :p3 ("Bull",    300, bag(:p1, :p2)),
  :p4 ("Pelle",   400, bag(:p1, :p2)),
  :p5 ("Mons",    500, bag(:p4, null)),
  :p6 ("Olle",    500, bag(:p1, :p2)),
  :p7 ("Birgitta",600, bag(:p1, :p2)),
  :p8 ("Murre",   700, bag(:p5, :p7));

You may also update stored functions using the set statement, for example

set income(:p1) = 300;

income(:p1)

Derived functions

A derived function is defined by a single expression.

Examples:

create function taxed_income(Person p) -> Real
  as income(p) * 0.4;

create function children(Person c) -> Bag of Charstring
  as select name(p)
       from Person p
      where c in parents(p);

taxed_income(:p1);
children(:p1)

Functions with result type Boolean implement predicates and return true when the condition is fulfilled.

Examples:

create function rich(Person p) -> Boolean
  as income(p)>600;
select name(p)
  from Person p
 where rich(p)

Aggregate functions

Functions whose arguments are declared Bag of define aggregate functions.

Example:

create function myavg(Bag of Number x) -> Number
  as sum(x)/count(x)

The following query computes the average income of Murre's parents:

select myavg(income(parents(p)))
  from Person p
 where name(p)="Murre"

Aggregate functions compute a single result from the elements in a bag.

Basic aggregate functions have one of their arguments declared as a bag and return a single result:

aggfn(Bag of Type1 x) -> Type2

Aggregate functions with a single argument can be used in group by.

Related documentation

Aggregate functions in queries are treated different from other functions in queries.

System aggregate functions

Overloaded functions

Function names may be overloaded, i.e., functions having the same 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.

For example, the following two function definitions create two resolvents of the overloaded function less():

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

Internally the system stores the resolvents under different mangled function names. 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 two resolvents above will be given the internal resolvent names NUMBER.NUMBER.LESS->BOOLEAN and CHARSTRING.CHARSTRING.LESS->BOOLEAN.

The query optimizer resolves the correct resolvent to apply based on the types of the arguments. The type of the result is not considered. If there is an ambiguity, i.e. several resolvents qualify in a call, or if no resolvent qualify, an error will be generated by the query compiler.

When overloaded function names are encountered in function bodies, the system will try to use local variable declarations to choose the correct resolvent (early binding).

For example:

create function richer(Person p,Person q)->Boolean
  as less(income(q),income(p));
richer(:p7,:p3)

Here the system will choose the resolvent NUMBER.NUMBER.LESS->BOOLEAN, since income() returns integers and the resolvent NUMBER.NUMBER.LESS->BOOLEAN is applicable to integers by inheritance. The other function resolvent CHARSTRING.CHARSTRING.LESS->BOOLEAN does not qualify since it cannot have integer arguments.

Another example:

create function ordered_names(Person p,Person q)->Boolean
   as less(name(p),name(q));

ordered_names(:p2,:p1)

Here the system will choose the resolvent CHARSTRING.CHARSTRING.LESS->BOOLEAN, since the function name() returns a string. In both cases above the type resolution (selection of resolvent) will be done at compile time.

Late binding

Sometimes it is not possible to determine the resolvent to choose based on its arguments, so the type resolution has to be done at run time. This is called late binding.

For example, suppose that managers are employees whose incomes are the sum of the income as a regular employee plus some manager bonus:

create type Manager under Person;
create function bonus(Manager)->Real as stored;
create function grossincome(Person p)->Real i 
  as income(p);
create function grossincome(Manager m)->Real i
  as select income(p) + bonus(m)
       from Person p
       where p = m

In this example the equality e = m is used for selecting the salary of the manager as a regular employee.

Now, suppose that we need a function that returns the gross incomes of all persons in the database, i.e. we use MANAGER.INCOME->INTEGER for managers and EMPLOYEE.INCOME->INTEGER for non-manager. Such a function is defined as:

create function grossincomes() -> Bag of (Charstring nm, Real i)
  as select name(p), grossincome(p)
       from Person p

Since income is overloaded with resolvents EMPLOYEE.INCOME->INTEGER and MANAGER.INCOME->INTEGER and both qualify to apply to all person, the resolution of income(p) will be done at run time. To avoid the overhead of late binding one may use casting, as explained next.

For example, let's add another type Manager under `Personand make :m1 manager:

create Manager (name, income, bonus) instances
           :m1 ("Fredrik", 600, 200);

grossincomes()

Casting expressions

The type of an expression can be explicitly defined using the casting statement, for example:

create function grossincome(Manager m)->Integer i
  as income(cast(m as Person)) + bonus(m);

select grossincome(p)
  from Person p
 where name(p)="Fredrik"

Deleting functions

Functions are deleted with the delete function statement.

Examples:

delete function rich

Deleting a function also deletes all functions calling the deleted function.