Creating and Managing Views

Context

If some columns in one or more tables in a database are frequently searched for, an administrator can define a view for these columns, and then users can directly access these columns in the view without entering search criteria.

Different from a base table, a view is a virtual table. Only view definition is stored in the database and view data is not. The data is stored in a base table. If data in the base table changes, the data in the view changes accordingly. A view is like a window through which users can know their interested data and data changes in the database. A view is triggered every time it is referenced.

Managing Views

• Create a view.
  Create the MyView view. In the command, tpcds.web_returns indicates the created user table that contains the wr_refunded_cash integer column.

  1 2	gaussdb=# CREATE OR REPLACE VIEW MyView AS SELECT * FROM tpcds.web_returns WHERE trunc(wr_refunded_cash) > 10000; CREATE VIEW

  

  The OR REPLACE parameter in this command is optional. It indicates that if the view exists, the new view will replace the existing view.

• Query a view.
  Query MyView.

  1	gaussdb=# SELECT * FROM MyView;

• Query views of the current user.

  1	gaussdb=# SELECT * FROM my_views;

• Query all views.

  1	gaussdb=# SELECT * FROM adm_views;

• Query details about a specified view.
  View details about MyView.

  1 2 3 4 5 6 7 8

  gaussdb=# \d+ MyView View "PG_CATALOG.MyView" Column | Type | Modifiers | Storage | Description ----------+-----------------------+-----------+----------+------------- USERNAME | CHARACTER VARYING(64) | | extended | View definition: SELECT PG_AUTHID.ROLNAME::CHARACTER VARYING(64) AS USERNAME FROM PG_AUTHID;

• Delete a view.
  Delete MyView.

  1 2	gaussdb=# DROP VIEW MyView; DROP VIEW