Skip to main content
Version: 7.0

FORMULA operator

The FORMULA operator creates a property that implements a custom formula.

Syntax​

FORMULA [NULL] [className [valueId]] implList [( paramList )] [NULL]

Where implList is defined as:

[syntaxType1] text1, ..., [syntaxTypeN] textN

And paramList is defined as:

classId1 [paramId1], ..., classIdK [paramIdK]

Description​

The FORMULA operator creates a property that executes an arbitrary formula in SQL. It is possible to specify different formulas for different SQL dialects so that these properties are portable between different DBMSs.

FORMULA is a context-independent property operator: it cannot appear inside expressions. Use it on the right-hand side of an = statement or anonymously inside brackets in a JOIN operator usage.

See the custom formula abstraction for what the operator produces — the default result class, the NULL-handling relaxations, and the table-valued mode. This article covers how those options are written.

Parameters​

  • NULL

    Keyword that loosens the default NULL-handling rules of a non-table-valued formula (table-valued mode ignores both positions). The formula text is executed as written, with the SQL values of its arguments, and the platform does not check that it follows the rules of its form: the non-NULL condition of the property — the condition used wherever the property serves as a condition, in a selection operator or in an assignment — is derived as described below, while a value read as is (in an export or on a form) is the result of the SQL text. In the two forms without the trailing keyword, an argument that is NULL by construction — the NULL literal or a local property without a value — makes the property NULL without executing the formula. The formula must therefore follow the rules of its form itself:

    • without the keyword — the property is non-NULL when all its arguments are non-NULL. The formula must return a non-NULL value for them and NULL when any argument is NULL: an SQL function that returns a value for NULL input (concat, COALESCE, string_to_array with a NULL delimiter) needs an explicit check (CASE WHEN $2 IS NULL THEN NULL ELSE ... END), otherwise the value read as is differs from the condition.
    • before className — the formula may also return NULL for non-NULL arguments: the property is non-NULL when the value computed by the formula is non-NULL, so the formula is executed over NULL arguments as well and must return NULL for them.
    • at the end — the formula is a union of its arguments: it is executed over NULL arguments, the NULL literal included, and the property is non-NULL when at least one argument is non-NULL. The formula must return a non-NULL value in that case and NULL when all arguments are NULL. Its result is not checked. If both keywords are given, this form applies.

  • className

    The name of the builtin class of the value returned by the property. If not specified, the resulting class is considered to be the common ancestor of all property operands. Required for table-valued formulas (see the parameter list below): the value column of the returned table needs an explicit type.

  • valueId

    Identifier or string literal placed right after className. Names the value column of the table returned by the formula — required for table-valued formulas (see the parameter list below) and ignored for non-table ones. The literal form is typically used to name an existing column in an external table, while the identifier form is typically used for the value produced by a set-returning function.

  • syntaxType1, ..., syntaxTypeN

    Keywords defining SQL dialect types. The following types are currently supported:

    • PG - PostgreSQL syntax
    • MS - MS SQL Server syntax

    If the dialect type is not specified explicitly, then the corresponding formula text is set as the default text. Each of the types (or the lack of a type) must appear in the operator no more than once.

  • text1, ..., textN

    String literals, each of which contains a formula in SQL syntax. Formula parameters are referenced as $1, $2, ... by position, or as $paramId when the parameter is named. Positional parameter numbers start from 1. When several implementations are supplied (one per dialect plus optionally a default), they must all describe the same parameter set — a mismatch in arity across them is rejected at parse time.

  • classId1 ... classIdK

    The classes of the formula's parameters. When the parameter list is omitted, parameter classes are inferred from the enclosing property declaration. The resulting property's arity is the larger of K and the highest positional index the formula text references. Positional references beyond K extend the list with auto-generated parameters.

  • paramId1 ... paramIdK

    Names by which the formula text refers to the parameters as $paramId instead of by position. Each is an identifier or a string literal. A parameter need not be named.

    Referenced parameters are passed as inputs into the SQL expression at evaluation time. Unreferenced ones become key columns of the returned table (see valueId above for the value column) and must match the column names the SQL expression returns. A single unreferenced INTEGER parameter named row is a shortcut: the platform supplies row numbers for it via SQL's ROW_NUMBER() OVER (), so the idiom works with any table-valued SQL expression.

Examples​

// a property with two parameters: a rounded number and the number of decimal places
round(number, digits) = FORMULA 'round(CAST(($1) as numeric),$2)';

// a property that converts the value passed as an argument to a 15-character string.
toString15(str) = FORMULA BPSTRING[15] 'CAST($1 AS character(15))';

// a property with two different implementations for different SQL dialects
jumpWorkdays = FORMULA NULL DATE PG 'jumpWorkdays($1, $2, $3)', MS 'dbo.jumpWorkdays($1, $2, $3)';

// named parameter references: $json / $field use names from the enclosing signature
field (JSON json, STRING field) = FORMULA JSON 'jsonb_extract_path($json, $field)';

// explicit parameter list declaring names for $date / $time references
toDateTime = FORMULA DATETIME '$date + $time' (DATE date, TIME time);

// table-valued formula: `row` is not referenced in the text, so it becomes a key column of the
// table produced by jsonb_array_elements; `value` names the value column feeding the result
array (JSON json, INTEGER row) = FORMULA JSON value 'jsonb_array_elements($json)';

// table-valued formula with a named key column: jsonb_each returns rows of (key, value);
// `key` (unused parameter) and `value` (valueId) align with the column names of that record
map (JSON json, STRING key) = FORMULA JSON value 'jsonb_each($json)';

// external-table mapping: the formula text is the table name, the string literal after
// STRING names the value column, and `key0` is the key column of that external table
country (LONG key0) = FORMULA STRING 'country_name' '_auto_country';

// trailing NULL: the formula is executed over NULL arguments, the property is non-NULL when at least one argument is
firstNonNull = FORMULA TEXT 'COALESCE($1, $2)' (TEXT name, TEXT login) NULL;