ALTER VIEW

语法

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

描述

“ALTER VIEW view_name AS select_statement;”用于改变已存在的视图的定义，语法效果与CREATE OR REPLACE VIEW类似。

“ALTER VIEW view_name SET TBLPROPERTIES table_properties;”中table_properties格式为 (property_name = property_value, property_name = property_value, ...)。

视图可以包含Limit和ORDER BY子句，如果关联视图的查询语句也包含了这类子句，则最后执行结果将根据视图的子句运算后得到。例如视图V指定了返回5条数据，而关联查询为select * from V limit 10，则最终只有5条数据返回。

限制

以上两种语法不可混用。

当视图包含分区，那么将无法通过这个语法来改变定义。

示例

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)