ALTER VIEW

Function

ALTER VIEW modifies all auxiliary attributes of a view. (To modify the query definition of a view, use CREATE OR REPLACE VIEW.)

Precautions

Syntax

• Set the default value of a view column.

  1 2	ALTER VIEW [ IF EXISTS ] view_name ALTER [ COLUMN ] column_name SET DEFAULT expression;

• Remove the default value of a view column.

  1 2	ALTER VIEW [ IF EXISTS ] view_name ALTER [ COLUMN ] column_name DROP DEFAULT;

• Change the owner of a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name OWNER TO new_owner;

• Rename a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name RENAME TO new_name;

• Set the schema of a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name SET SCHEMA new_schema;

• Set the options of a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name SET ( { view_option_name [ = view_option_value ] } [, ... ] );

• Reset the options of a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name RESET ( view_option_name [, ... ] );

Parameter Description

• IF EXISTS

  If this option is used, no error is generated when the view does not exist, and only a message is displayed.

• view_name

  Specifies the view name, which can be schema-qualified.

  Value range: a string. It must comply with the naming convention.

• column_name

  Specifies an optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.

  Value range: a string. It must comply with the naming convention.

• SET/DROP DEFAULT

  Sets or deletes the default value of a column. This parameter does not take effect.

• new_owner

  Specifies the new owner of a view.

• new_name

  Specifies the new view name.

• new_schema

  Specifies the new schema of the view.

• view_option_name [ = view_option_value ]

  Specifies an optional parameter for a view.

  • security_barrier: specifies whether the view provides row-level security. The value is of the Boolean type. The default value is true.
• expression

  Specifies constants, functions, or SQL expressions.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- Create a schema.
gaussdb=# CREATE SCHEMA tpcds;

-- Create the tpcds.customer table.
gaussdb=# CREATE TABLE tpcds.customer
(
c_customer_sk         INTEGER           NOT NULL,
c_customer_id         CHARACTER(16)     NOT NULL
);
 
-- Insert multiple records into the table.
gaussdb=# INSERT INTO tpcds.customer VALUES (1, 'AAAAAAAABAAAAAAA'),(100, 'AAAAAAAACAAAAAAA'),(150, 'AAAAAAAADAAAAAAA');

-- Create a view consisting of rows with c_customer_sk less than 150.
gaussdb=# CREATE VIEW tpcds.customer_details_view_v1 AS
    SELECT * FROM tpcds.customer
    WHERE c_customer_sk < 150;

-- Rename a view.
gaussdb=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Change the schema of a view.
gaussdb=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete a view.
gaussdb=# DROP VIEW public.customer_details_view_v2;

-- Delete the tpcds.customer table.
gaussdb=# DROP TABLE tpcds.customer;

-- Delete a schema.
gaussdb=# DROP SCHEMA tpcds CASCADE;

Helpful Links

CREATE VIEW and DROP VIEW