User Defined Transform

A user-defined transform is a user-defined function that returns a set of rows or a stream of rows. Its input arguments can be scalars or cursors. A cursor is an object representing a subquery, which the user-defined transform can use to read the subquery results. The subquery can be relational or streaming.

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")));