Defining Expression-based Columns

Go Up to Defining Columns

A computed column is one whose value is calculated each time the column is accessed at run time. The syntax is:

<col_name> COMPUTED [BY] (<expr>);

If you do not specify the data type, InterBase calculates an appropriate one. <expr> is any arithmetic expression that is valid for the data types in the columns; it must return a single value, and cannot be an array or return an array. Columns referenced in the expression must exist before the COMPUTED [BY] clause can be defined.

For example, the following statement creates a computed column, FULL_NAME, by concatenating the LAST_NAME and FIRST_NAME columns.

CREATE TABLE EMPLOYEE
(FIRST_NAME VARCHAR(10) NOT NULL,
 LAST_NAME VARCHAR(15) NOT NULL,
 FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME));

The next example creates a table with a calculated column (NEW_SALARY) using the previously created EMPNO and SALARY domains.

CREATE TABLE SALARY_HISTORY (EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERCENT_CHANGE DOUBLE PRECISION
DEFAULT 0
NOT NULL
CHECK (PERCENT_CHANGE BETWEEN –50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO)
ON UPDATE CASCADE
ON DELETE CASCADE);

Note: Constraints on computed columns are not enforced, but InterBase does not return an error if you do define such a constraint.