VIEW

创建视图

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

创建内部表视图

1. 创建内部表，示例语句如下。

   create table holo_test (
     amount decimal(10, 2), 
     rate decimal(10, 2)
   );
   insert into holo_test values 
   (12.12,13.13),
   (14.14,15.15),
   (16.16,17.17),
   (17.1,17),
   (18.01,19);

2. 基于内部表创建视图并查询表数据，示例语句如下。

   create view holo_view as select * from holo_test;
   
   select * from holo_view;
    amount | rate
   --------+-------
     12.12 | 13.13
     14.14 | 15.15
     16.16 | 17.17
     17.10 | 17.00
     18.01 | 19.00
   (5 rows)

创建外部表视图

1. 创建外部表，示例语句如下。

   create foreign table if not exists holo_foreign_test (
     amount decimal(10, 2), 
     rate decimal(10, 2)) 
     server odps_server 
     options(project_name '<projectname>', table_name '<odps_name>')
     );
     
   select * from holo_foreign_test limit 2;

2. 基于外部表创建视图并查询表数据，示例语句如下。

   create view foreign_view as select * from holo_foreign_test;
   
   select * from foreign_view limit 2;
    amount | rate
   --------+-------
     12.12 | 13.13
     14.14 | 15.15

创建内部表及外部表的联合视图

create view view1 as select * from holo_view union all  select * from foreign_view;

select * from view1;
 amount | rate
--------+-------
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
  17.10 | 17.00
  18.01 | 19.00
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
(20 rows)

删除视图

drop view <view_name>;

查看所有视图和视图的DDL

--sql命令
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

create extension hg_toolkit;
select public.hg_dump_script('viewname');