FirebirdSQL logo
 DOMAININDEX 

Computed Columns

Computed columns can be defined with the COMPUTED [BY] or GENERATED ALWAYS AS clause (the SQL standard alternative to COMPUTED [BY]).Specifying the data type is optional;if not specified, the appropriate type will be derived from the expression.

If the data type is explicitly specified for a calculated field, the calculation result is converted to the specified type.This means, for instance, that the result of a numeric expression could be converted to a string.

In a query that selects a computed column, the expression is evaluated for each row of the selected data.

Tip

Instead of a computed column, in some cases it makes sense to use a regular column whose value is calculated in triggers for adding and updating data.It may reduce the performance of inserting/updating records, but it will increase the performance of data selection.

Defining an Array Column

  • If the column is to be an array, the base type can be any SQL data type except BLOB and array.

  • The dimensions of the array are specified between square brackets.

  • For each array dimension, one or two integer numbers define the lower and upper boundaries of its index range:

    • By default, arrays are 1-based.The lower boundary is implicit and only the upper boundary need be specified.A single number smaller than 1 defines the range num…​1 and a number greater than 1 defines the range 1…​num.

    • Two numbers separated by a colon (‘:’) and optional whitespace, the second greater than the first, can be used to define the range explicitly.One or both boundaries can be less than zero, as long as the upper boundary is greater than the lower.

  • When the array has multiple dimensions, the range definitions for each dimension must be separated by commas and optional whitespace.

  • Subscripts are validated only if an array actually exists.It means that no error messages regarding invalid subscripts will be returned if selecting a specific element returns nothing or if an array field is NULL.