ALTER VIEW

Syntax

• ALTER VIEW view_name AS select_statement;
• ALTER VIEW view_name SET TBLPROPERTIES table_properties;

Description

ALTER VIEW view_name AS select_statement; is used to change the definition of an existing view. Its syntax effect is similar to that of CREATE OR REPLACE VIEW.

The format of table_properties in ALTER VIEW view_name SET TBLPROPERTIES table_properties; is (property_name = property_value, property_name = property_value, ...).

A view can contain the Limit and ORDER BY clauses. If the query statement of the associated view also contains these clauses, the final execution result is obtained from the calculation based on the clauses of the view. For example, if view V is specified to return five pieces of data and the associated query result is select * from V limit 10, only five pieces of data are returned.

Remarks

The preceding two syntax statements cannot be used together.

When a view contains partitions, the definition cannot be changed by using this syntax.

Example

CREATE OR REPLACE VIEW tv_view as SELECT id,name from (values (1, 'HetuEngine')) as x(id,name);
 
SELECT * FROM tv_view;
 id | name 
----|------
  1 | HetuEngine   
(1 row)
 
ALTER VIEW tv_view as SELECT id, brand FROM (VALUES (1, 'brand_1', 100), (2, 'brand_2', 300) ) AS x (id, brand, price);
 
SELECT * FROM tv_view;
 id |  brand  
----|---------
  1 | brand_1 
  2 | brand_2 
(2 rows)
 
ALTER VIEW tv_view SET TBLPROPERTIES ('comment' = 'This is a new comment');
 
show tblproperties tv_view;
                           SHOW TBLPROPERTIES                         
--------------------------------------------------------------------
        comment                 'This is a new comment'                          
        presto_query_id         '20210325_034712_00040_f63xj@default@HetuEngine' 
        presto_version                                                         
        presto_view             'true'                                           
        transient_lastDdlTime   '1616644032'                                                                                                     
(1 row)