• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Quick Start
  5. Other Operations
  6. Creating and Managing Views

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.

A view is different from a basic table. It is only a virtual object rather than a physical one. A database only stores the definition of a view and does not store its data. The data is still stored in the original 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 a View

  • Creating a view
    Run the following command to create MyView:
    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:
    SELECT * FROM MyView;
  • Run the following command to query the views in the current user:
    SELECT * FROM user_views;
  • Run the following command to query all views:
    SELECT * FROM dba_views;
  • View details about a specified view.
    Run the following command to view details about the dba_users view:
    \d+ dba_users
                          View "PG_CATALOG.DBA_USERS"
      Column  |         Type          | Modifiers | Storage  | Description
    ----------+-----------------------+-----------+----------+-------------
     USERNAME | CHARACTER VARYING(64) |           | extended |
    View definition:
     SELECT PG_AUTHID.ROLNAME::CHARACTER VARYING(64) AS USERNAME
       FROM PG_AUTHID;
  • Deleting a view
    Run the following command to delete MyView:
    DROP VIEW MyView;
    DROP VIEW