Creating and Managing Views

Background

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.

A view is different from a base table. It is only a virtual object rather than a physical one. 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. In this sense, 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.

  Run the following command to create MyView:

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

  NOTE: 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.

  Run the following command to query MyView:

  openGauss=# SELECT * FROM MyView;
  

• View details about a specified view.

  Run the following command to view details about MyView:

  openGauss=# \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.

  Run the following command to delete MyView:

  openGauss=# DROP VIEW MyView;
  DROP VIEW