Skip to main content

Stateful and procedural functions

Stateful functions​

A stateful function is a function whose internal state may change when it is called. For example, the wall clock function like now() and random number generators like rand(Integer high)->Integer are stateful.

Stateful functions can be called in queries. The query optimizer is aware of that they may return different values for given arguments every time they are called. Therefore the optimizer is careful not to change the order in which stateful functions are called in a query.

note

The stream function extract(s) is stateful since it runs stream s to extract the elements. The state of stream s may thereby change. For example, a stream reading sensor values will continuously change its state when new sensor readings arrive.

The function stateful(Charstring pat)->Bag of Charstring returns the signatures of the stateful resolvents for functions whose names match pat.

Example:

stateful('%rand%')

Procedural functions​

A procedural function is a function defined as a sequence of OSQL statements that may have side effects. The typical use of procedural functions is for consistent updates of the database. The return statement inside a procedural function returns a value.

Example: Let's create a table to store equipment properties.

drop table Equipment; -- Drop old table
create table Equipment(name Charstring,
                       weight Integer,
                       unique(name))

The following procedural Boolean function new_equipment(nm,e) inserts a new item into the equipment table and returns true if the insert succeeded:

create function new_equipment(Charstring n, Integer w) -> Boolean
  as {
       insert into Equipment values (n, w);
       return rowcount()=1;
     }
note

You can have several OSQL statements in the body of a procedural function by enclosing them in curly brackets {...}.

note

The stateful function rowcount() returns the number of affected rows in a preceding update of a table.

Test it:

new_equipment('Sensor',10);
select * from Equipment

The function new_equipment is stateful:

stateful('new_equipment')

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.

Iterative updates​

Use SQL's update statement in procedural functions for iterative updates of table rows fulfilling some conditions.

Example:

create function increase_weights(Integer inc,Integer thres) -> Integer oldinc
  as {
       update Equipment
          set weight = weight + inc
        where weight>thres;
       return rowcount();
     }

Let's test it:

increase_weights(100,5);
select * from Equipment