opnGauss SQL Syntax - View

define view

⚫ A view is a virtual table derived from one or several basic tables, which can be used to control user access to data. The SQL statements involved are shown in the following table.

⚫ Description:

 The view is different from the basic table. The database only stores the definition of the view, not the data corresponding to the view. These data are still stored in the original basic table.

 If the data in the basic table changes, the data queried from the view also changes.

 In this sense, a view is like a window through which data and changes in the database of interest to the user can be seen.

Create a view

⚫ Syntax

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ] [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ] AS query;

⚫ Example:

 Create the view privilege_view and update the view if it exists.

postgres=# CREATE OR REPLACE VIEW privilege_view AS SELECT b_number, b_type FROM bank_card;

 View the data in the view, the syntax is the same as the query table.

postgres=# SELECT * FROM privilege_view;

 View the view structure.

postgres=# \d privilege_view;

delete view

⚫ Syntax

DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

⚫ Parameter description

 IF EXISTS

◼ If the view exists, delete it.

 view_name

◼ The view to be deleted.

 CASCADE | RESTRICT

◼ CASCADE: Cascading delete objects that depend on this view (such as other views).

◼ RESTRICT: Refuse to drop this view if any dependent objects exist. This option is the default.

⚫ Example

postgres=# DROP VIEW IF EXISTS privilige_view;