In database management, we often need to dynamically generate new data columns based on existing data. The virtual column feature in GBase 8s provides an efficient way to meet this requirement. This article will detail the concept, definition, use cases, and related limitations of virtual columns in GBase 8s.

1. Definition of Virtual Columns

A virtual column is a data column defined using an expression or function. Logically, the virtual columns of a table have the same syntax as ordinary columns, but the values of virtual columns are not stored on any physical storage medium. Instead, they are calculated during the execution of SQL based on the expression or function defining the virtual column.

Key Points:

1. Similar to ordinary columns, with no significant difference in use, except that values are calculated using expressions.
2. In the expression of a virtual column, you can include other columns of the same table, constants, SQL functions, and even some user-defined functions.
3. The value of a virtual column can only be seen when queried; unlike ordinary columns, it is not permanently stored on the disk. The value is calculated dynamically based on the expression.

For example:

create table t1 (id int, month_sal decimal(10,2,total_sal as(month_sal*12));

Here, total_sal is a virtual column that returns the value of month_sal * 12.

2. Syntax Explanation

• column: The name of the virtual column. The naming rules and constraints are consistent with the ordinary columns in the current version of GBase 8s. It cannot have the same name as other columns in the table and cannot be omitted.
• datatype: The data type of the virtual column. It supports the built-in data types of the current version of GBase 8s and can be omitted. If omitted, the data type of the virtual column matches the return type of the expression or function defining the virtual column (large objects, ROW, collections, and SERIAL types are not supported).
• GENERATED ALWAYS: Explicit declaration of the virtual column keyword, which can be omitted.
• AS: Explicit declaration of the virtual column keyword, which cannot be omitted.
• column_expression: The expression or constant used to define the virtual column. The expression can only reference columns in the current table and must have a unique return value. It cannot be omitted or reference other virtual columns.

For example:

-- Create table sc, where v_source is a virtual column.
CREATE TABLE sc (
    stu_id INT PRIMARY KEY,
    stu_nm VARCHAR2(50),
    course_id INT,
    source DECIMAL(10,2),
    v_source VARCHAR(30) AS (
        CASE 
            WHEN source < 60 THEN 'Fail'
            WHEN source >= 60 THEN 'Pass'
        END
    ) VIRTUAL
);

Insert data and then query, you can see that the value of the virtual column is dynamically generated based on the definition.

INSERT INTO sc (stu_id, stu_nm, course_id, source) VALUES (1, 'Zhang San', 9001, 56);
INSERT INTO sc (stu_id, stu_nm, course_id, source) VALUES (2, 'Li Sisi', 9001, 80);

SELECT * FROM sc;

Output:

stu_id  | stu_nm    | course_id | source | v_source
--------|-----------|-----------|--------|----------
1       | Zhang San | 9001      | 56.00  | Fail
2       | Li Sisi   | 9001      | 80.00  | Pass

Data Type Usage Restrictions

• Large objects, ROW, custom types, and collections are not supported.
• SERIAL, SERIAL8, and BIGSERIAL are not supported.

Scope of Expressions

• Only columns in the current table can be referenced, and the expression must have a unique return value.
• Supports single column, constant expressions, conditional expressions, and function expressions.
• Supports user-defined functions and functions defined in PACKAGES.
• Cannot reference other virtual columns.
• Pseudo columns are not supported.
• Aggregate functions, LISTAGG(), and column-to-row functions are not supported.

3. Usage of Virtual Columns

In DDL

• Can be defined in CREATE TABLE.
• Can be added via ALTER TABLE ADD Col.
• Can be modified via ALTER TABLE MODIFY Col.

Modification Restrictions

• Data type and expression can be modified.
• Columns referenced by the expression cannot be modified.
• Cannot change a virtual column to an ordinary column or vice versa.
• Can be deleted via ALTER TABLE DROP Col.
• Cannot delete a column referenced by a virtual column directly; the virtual column must be deleted first.
• Supports comments via COMMENT.
• Does not support DEFAULT expressions.
• CREATE AS SELECT is not supported.

Constraints and Indexes

DML Usage

• UPDATE statements on virtual columns are not allowed.
• Can be used in the WHERE clause of UPDATE/DELETE.
• Supports INSERT INTO t1 SELECT * FROM t2.

DQL Usage

• Cannot be used in GROUP BY clauses.
• Other query syntaxes are supported.

4. Querying Virtual Column Attributes

System Tables

• SYSCOLUMNS
  • COLATTR field: new values 256 or 768 indicate virtual columns. 768 if the data type is specified explicitly, 256 otherwise.

SELECT DISTINCT t.tabname, sysc.colname, sysc.colattr
FROM systables t, syscolumns sysc
WHERE sysc.tabid = t.tabid AND t.tabname = 'sc';

Output:

tabname | colname  | colattr
--------|----------|--------
sc      | course_id| 0
sc      | source   | 0
sc      | stu_id   | 128
sc      | stu_nm   | 0
sc      | v_source | 768

• SYSDEFAULTSEXPR
  • New VTCOL field: indicates whether the column is a virtual column (1) or a default expression (0).

SELECT t.tabname, d.colno, d.vtcol, d.default
FROM sysdefaultsexpr d, systables t
WHERE d.tabid = t.tabid AND t.tabname = 'sc' AND d.type = 'T';

Output:

tabname | colno | vtcol | default
--------|-------|-------|--------------------------------
sc      | 5     | 1     | CASE WHEN (source < 60.00) THEN
sc      | 5     | 1     | 'Fail' WHEN (source >= 60.00)
sc      | 5     | 1     | THEN 'Pass' END

As an advanced feature of the GBase 8s database, virtual columns provide greater flexibility for data operations. Through this introduction, we have learned about the definition, creation, usage, and related restrictions of virtual columns. We hope this information helps you utilize the virtual column feature more effectively and enhance database management efficiency.