User Defined Function

A UDF is a User-Defined Routine that returns a scalar value. A user-defined function (UDF) can implement complex calculations or interact with an external system, taking zero or more scalar parameters and returning a scalar result. UDFs operate like built-in functions such as FLOOR() or LOWER(). SQLstream allows the creation of Java or SQL functions.

The following describes the differences between user-defined functions, procedures, and transforms:

User-Defined Function (UDF)

SQL: CREATE FUNCTION

Accepts ... Zero or more parameters

Returns ... Scalar value

  • A UDF is used in a scalar expression; it takes (0 .. n) scalar arguments and returns a scalar value.
  • When used in a query, a scalar expression is evaluated for each row.
  • SQLstream allows the creation of Java or SQL functions.

User-Defined Procedure (UDP)

SQL: CREATE PROCEDURE

Accepts ... Zero or more parameters

Returns ... No values returned A UDP is just a UDF that returns no value; it is evaluated for a side effect.

User-Defined Transform (UDX)

SQL: CREATE FUNCTION

Accepts ... Zero or more Relations

Returns ... A Streaming Relation

  • A UDX is different: its output is a stream of rows, and its inputs can be scalars or be streams. In SQL,
  • a streaming argument to a UDX is represented as CURSOR(SELECT STREAM ...)
  • and a scalar is represented by any scalar expression.

The invocation of a UDX is quite often preceded by the keyword TABLE. For example:

SELECT STREAM *
 FROM TABLE(
  filterSignal( CURSOR( SELECT STREAM * FROM "RawSignals")));