SQL-like query language for csv

User Defined Function

A User Defined Function is a routine that can be called just like built-in functions. A function has some input parameters, and returns a single value.

Functions create local scopes. Variables, cursors, temporary tables, and functions declared in user defined functions can be refered only within the functions.

Scalar Function

Declaration

scalar_function_declaration
  : DECLARE function_name FUNCTION ([parameter [, parameter ...] [, optional_parameter ...]])
    AS
    BEGIN
      statements
    END;

optional_parameter
  : parameter DEFAULT value
function_name
identifier
statements
Statements
parameter
Variable
value
value

A scalar function takes some arguments, and returns a value. In the statements, arguments are set to variables specified in the declaration as parameters.

Usage

function_name([argument, [, argument ...]])
function_name
identifier
argument
value

Aggregate Function

Declaration

aggregate_function_declaration
  : DECLARE function_name AGGREGATE (cursor_name [, parameter ...] [, optional_parameter ...])
    AS
    BEGIN
      statements
    END;

optional_parameter
  : parameter DEFAULT value
function_name
identifier
cursor_name
identifier
statements
Statements
parameter
Variable
value
value

An aggregate function takes at least one argument, and returns a value. The first argument is a representation of grouped values, and the following arguments are parameters.

In the statements, grouped values represented by the first argument can be referred with a pseudo cursor named as cursor_name, and the second argument and the followings are set to variables specified in the declaration as parameters. You can use the Fetch Statement, While In Statement or the Cursor Status Expressions against the pseudo cursor.

Usage

You can use a user defined aggregate function as an Aggregate Function or an Analytic Function.

As an Aggregate Function
function_name([DISTINCT] expr [, argument ...])
function_name
identifier
expr
value
argument
value
As an Analytic Function
function_name([DISTINCT] expr [, argument ...]) OVER ([partition_clause] [order_by_clause [windowing_clause]])
function_name
identifier
expr
value
argument
value
partition_clause
Partition Clause
order_by_clause
Order By Clause
windowing_clause
Windowing Clause

Example:

DECLARE product AGGREGATE (list, @default DEFAULT 0)
AS
BEGIN
    VAR @value, @fetch;

    WHILE @fetch IN list
    DO
        VAR @floatVal := FLOAT(@fetch);
        
        IF @floatVal IS NULL THEN
            CONTINUE;
        END IF;

        IF @value IS NULL THEN
            @value := @floatVal;
            CONTINUE;
        END IF;

        @value := @value * @floatVal;
    END WHILE;
    
    IF @value IS NULL THEN
        @value := @default;
    END IF;

    RETURN @value;
END;

SELECT product(i) FROM numbers;

SELECT product(i, NULL) FROM numbers;

SELECT i, product(i) OVER (order by i) FROM numbers;

DISPOSE FUNCTION Statement

A DISPOSE FUNCTION statement disposes user defined function named as function_name.

DISPOSE FUNCTION function_name; 
function_name
identifier

RETURN Statement

A RETURN statement terminates executing function, then returns a value. If the return value is not specified, then returns a null.

When there is no return statement, the function executes all of the statements and returns a null.

RETURN [value];
value
value