PolarDB:CREATE VIEW

Overview

CREATE VIEW defines the view of a query. The created views do not save data replicas in the database. When the view is referenced by a query, the database dynamically generates results based on the query statements that you define.

CREATE OR REPLACE VIEW creates a new view or replaces an existing view that has the same name. By default, PolarDB allows you to change the order of columns, and add or remove columns in a new view definition. If you want this command to behave in the same way as the command in the PostgreSQL community edition, disable the polar_enable_or_replace_view_alter_column parameter. After you disable this parameter, you must define the new query to generate the same columns as the existing view, including the column order, column names, and data types. However, you can add new columns to the end of the column list. The data source and computing logic of the new columns can be different from existing columns.

If you specify a schema name in the statement, the view is created in the specified schema. For example, if you execute the CREATE VIEW myschema.myview ... statement, the myview view is created in the myschema schema. Otherwise, the view is created in the current schema. Because temporary views are created in a special schema, you cannot specify the schema name when you create a temporary view. The name of a view must be unique in a schema and must not be the same as the name of another view, table, sequence, index, or external table.

Syntax

   CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

Parameters

• TEMPORARY/TEMP

  Creates a temporary view. A temporary view is automatically deleted at the end of the current session. If a temporary view exists, any existing non-temporary views that have the same name as the temporary view are hidden from the current session. You can explicitly reference such views by using schema-qualified names.

  If a view references at least one temporary table, the view is automatically created as a temporary view, even if the TEMPORARY parameter is not explicitly specified.

• RECURSIVE

  Creates a recursive view. The following syntax is used:

  CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;

  The created view is the same as the view that is created by using the following syntax:

  CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;

  Note

  You need to specify a list of column names for a recursive view.

• name

  Specifies a name of the created view. You can choose to specify a schema or not.

• column_name

  Specifies a list of column names for the created view. This parameter is optional. If you do not specify this parameter, the column names are automatically inferred from the query results.

• WITH (_view_option_name_[=_view_option_value_] [, ... ] )

  Specifies a set of optional parameters for the view. The following parameters are supported:

  • check_option(enum)

    You can set this parameter to local or cascaded. The behavior of this parameter is the same as the statement WITH [ CASCADED | LOCAL ] CHECK OPTION. You can use the ALTER VIEW command to modify this parameter of an existing view.

  • security_barrier(boolean)

    Enables or disables the RLS feature for the view.

• query

  Defines the row and column structure of a view. A SELECT or VALUES statement is used.

• WITH [ CASCADED | LOCAL ] CHECK OPTION CHECK OPTION

  Specifies whether the new rows that are generated by INSERT and UPDATE statements must meet the conditions defined in the view. If the new rows meet the conditions, they are visible in the view. If the new rows does not meet the conditions, the insert or update operation is rejected. If you do not specify CHECK OPTION, new rows that are generated by INSERT and UPDATE statements may not be visible in the view. The following check options are supported:

  • LOCAL: New rows are checked based on only the conditions defined in the current view. The conditions defined in the underlying views are not checked, unless you also specify CHECK OPTION for them.

  • CASCADED: New rows are checked based on the conditions defined in the current view and underlying views. If you specify CHECK OPTION but do not specify LOCAL or CASCADED, CASCADED is used by default. We recommend that you do not use CHECK OPTION for RECURSIVE views.

  • CHECK OPTION: Only updatable views that do not contain INSTEAD OF triggers or INSTEAD rules support this option. If an updatable view is created based on a basic view that contains INSTEAD OF triggers, you can specify LOCAL CHECK OPTION to check new rows based on the conditions defined in the updatable view. However, the conditions defined in the basic view are ignored. In addition, CASCADED CHECK OPTION is not cascaded to the views that are updatable by triggers. The conditions that are directly defined in such views are ignored.

    If the view or its underlying relationships contain INSTEAD rules, the INSERT or UPDATE statements may be changed by the rules. In this case, the new query ignores the conditions that are defined directly tn the view and the conditions defined in the underlying relationships that contain INSTEAD rules.

Usage notes

Make sure that you specify the column names and types when you create the view. Sample code:

CREATE VIEW vista AS SELECT 'Hello World';

We recommend that you specify the column names and types based on your requirements instead of using the default column name ?column? and data type text. You can specify descriptive column names and data types to avoid potential risks. The best practice is to give each string in the view results a specific name. See the following sample code:

CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

For tables that are referenced in a view, the access permissions to the table depends on the permissions of the view owner. This provides secure and limited access to the underlying tables in some cases. However, not all views are safe against data tampering. You also need to pay attention to the permissions of the functions that are used in a view. Such functions are directly called from the query of the view. Therefore, the view user must have the permissions to execute all functions that are referenced in the view.

When you modify an existing view by using CREATE OR REPLACE VIEW, only the definition of the view (the SELECT statement) is updated. Other properties of the view including the ownership, permissions, and rules that are not defined by the SELECT statement remain unchanged. To replace a view, you must be the owner of the view or a user who has the owner role of the view.

Updatable views

Simple views support automatic updates. You can execute INSERT, UPDATE, and DELETE statements on simple views the same way as you do on regular tables. An updatable view must meet the following conditions:

• The FROM clause of the view contains only one table or updatable view.

• The top-level SELECT statement of the view definition does not contain WITH clauses, DISTINCT keywords, GROUP BY clauses, HAVING clauses, LIMIT clauses, or OFFSET clauses.

• The top-level SELECT statement of the view definition does not contain set operators, including UNION, INTERSECT, and EXCEPT.

• The list of the SELECT statement of the view definition does not contain aggregate functions, window functions, or functions that return collections.

An updatable view can contain both updatable and read-only columns at the same time. If a column of a view directly references an updatable column in the underlying basic table, this column is updatable. Otherwise, the column is read-only. If you try to assign values to the column by using INSERT or UPDATE statements, an error is reported.

When INSERT, UPDATE, or DELETE statements are executed on an updatable view, the system automatically executes these statements on its underlying basic tables. In addition, INSERT statements that contain ON CONFLICT UPDATE clauses are fully supported.

If the definition of an updatable view contains a WHERE clause, it defines which rows of the underlying tables can be updated by UPDATE and DELETE statements that are executed on the view. However, the rows that are updated by such UPDATE statements may no longer meet the conditions that are defined in the WHERE clause and become invisible in the view. Similarly, INSERT statements may add new rows that do not meet the conditions that are defined in the WHERE clause to the underlying table. Such rows are also invisible in the view. ON CONFLICT UPDATE may also affect existing rows that are invisible in the view.

You can use CHECK OPTION to prevent INSERT or UPDATE statements from adding or updating rows that are invisible in the view. This parameter ensures that the rows that are inserted or updated by executing INSERT or UPDATE statements on the view meet the conditions defined in the WHERE clause. As a result, the rows are visible in the view.

For an updatable view whose security_barrier property is configured, the conditions defined in the WHERE clause of the view definition and the conditions that contain the LEAKPROOF operators are checked before any other conditions provided by view users. This allows security barrier conditions to take effect before the conditions in the WHERE clauses that are provided by the users.

The views that are not qualified as updatable views are complex views. Complex views are read-only by default. The system does not allow insert, update, or delete operations on these views. To make a complex view updatable, you can create a INSTEAD OF trigger in the view. If insert, update, or delete operations are performed on the view, this trigger tries to perform the corresponding operations on the underlying tables. For more information about creating triggers. Alternatively, you can create rules to achieve the same goal. However, triggers are usually easier to understand and use.

The user who performs insert, update, or delete operations on the view must have the corresponding permissions to perform such operations on the view. In addition, the owner of the view must have corresponding permissions on the underlying basic tables.

Examples

• Create a view that consists of all comedy films.

      CREATE VIEW comedies AS
          SELECT *
          FROM films
          WHERE kind = 'Comedy';

  The created view contains the columns of the films table. Although * is used to create the view, the columns that are added to the films table after the view is created are not included in the view.

• Create a view that consists of all film names.

  CREATE VIEW film_names AS
      SELECT title
      FROM films;

• By default, PolarDB allows you to use one of the following syntaxes to add the release field in the film_names view.

  Syntax 1:
  CREATE OR REPLACE VIEW film_names AS
      SELECT title, release
      FROM films;
  
  Syntax 2:
  CREATE OR REPLACE VIEW film_names AS
      SELECT release, title
      FROM films;

  Note

  If the polar_enable_or_replace_view_alter_column parameter is disabled, only syntax 1 can be used.

• Create a view that contains LOCAL CHECK OPTION.

  CREATE VIEW universal_comedies AS
      SELECT *
      FROM comedies
      WHERE classification = 'U'
      WITH LOCAL CHECK OPTION;

  A view is created based on the comedies view. The view displays only the films whose kind parameter is set to Comedy and classification parameter is set to U. If you try to execute an INSERT or UPDATE statement that does not specify classification = 'U' for the inserted or updated row, the execution fails. However, the kind parameter is not checked in this process.

• Create a view that contains CASCADED CHECK OPTION.

  CREATE VIEW pg_comedies AS
      SELECT *
      FROM comedies
      WHERE classification = 'PG'
      WITH CASCADED CHECK OPTION;

  A view is created based on the kind and classification parameters. New rows are checked based on the conditions.

• Create a view that contains both updatable columns and read-only columns.

  CREATE VIEW comedies AS
      SELECT f.*,
              country_code_to_name(f.country_code) AS country,
              (SELECT avg(r.rating)
              FROM user_ratings r
              WHERE r.film_id = f.id) AS avg_rating
      FROM films f
      WHERE f.kind = 'Comedy';

  The created view supports INSERT, UPDATE, and DELETE statements. All columns that reference the films table are updatable. The computed columns country and avg_rating are read-only.

• Create a recursive view that consists of numbers from 1 to 100.

  CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
      VALUES (1)
  UNION ALL
      SELECT n+1 FROM nums_1_100 WHERE n < 100;

  Note

  In the preceding SQL statement, although the name of the recursive view is schema-qualified, its internal self-reference is not schema-qualified. This is because the name of an implicitly created CTE cannot be schema-qualified.