Procedural and stateful functions

🚀 Interactive documentation

This page uses Wasm code blocks so you can run the examples directly in the browser.

A procedural function is a function defined as a sequence of OSQL statements that may have side effects, e.g. database update statements. Most OSQL statements are allowed in procedure bodies.

Procedural functions can be called in queries. The query optimizer is aware of that procedural functions may change the state of the database when it is called. Therefore the optimizer is careful not to change the order in which such stateful functions are called in a query.

Notice that also other functions than procedural functions can be stateful in that they are affected by some internal state. For example, wall clock functions like now() and random generators such as rand(low,high) are stateful. Also the operator e in s is stateful when its second argument ´s´ is a stream, i.e. when elements e are extracted from s: . The reason is that a stream may continuously change its state, e.g. when new sensor readings arrive.

You can check whether a function f is stateful by the function stateful(f), for example:

select doc(f)
  from Function f
 where f in apropos("log")
   and stateful(f)

The following procedural function new_equipment(nm,e) creates a new object of type Equipment and sets the properties name() and weight(), i.e. it is a constructor for equipment:

create type Equipment;
create function name(Equipment) -> Charstring as stored;
create function weight(Equipment) -> Real as stored;

create function new_equipment(Charstring nm,Integer w) -> Equipment e
  as { create Equipment instances e;
       set name(e)=nm;
       set weight(e)=w;
       return e;
     }

Example of use:

set :e = new_equipment('Sensor',10);
name(:e);
weight(:e)

Iterating over results

The for each statement iterates over the result of a query by executing the for each body for each result variable binding of the query. For example the following procedural function adds inc to the weights of all equiment having a weight higher than limit and returns a bag of their old weights:

create function increase_weight(Integer inc,Integer thres) -> Integer oldinc
  as for each Equipment e, Real w
        where w > thres
          and w = weight(e)
         { return w;
           set weight(e) = w + inc;
         };

The for each statement does not return any value at all unless a return statement is called within its body as in increase_incomes().

If the return statement is not called in the procedural function, the result is empty. If a procedural function is used for its side effects only, not returning any value, the result type Boolean can be specified.