PostgreSQL 9.3物化视图使用
物化视图在Oracle里面是很早就内置的一个功能，而PostgreSQL也很早就将功能代码做出来，方式是类似create table as....，只是一直没有内置，9.3版本终于将此作为一个内置的功能点来使用，下面分享下最新版本的物化视图使用。
目前postgres9.3在官网上有4个安装包，分别是9.3.0(stable version)、9.3.0 beta1、9.3.0betal2和9.3.0rc版本(release candidate version)，所以我们
下载稳定的9.3.0 stable版本。
下载地址： http://www.postgresql.org/ftp/source/v9.3.0/
安装略。
一、语法
CREATE MATERIALIZED VIEW table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
二、说明
storage_parameter是存储参数，诸如填充因子(fillfactor)等,tablespace可以指定表空间,比较关键的是后面的as query with [no] data,后面示例描述
三、示例
1.创建基础表
[postgres@primary ~]$ psql
psql (9.3.0)
Type "help" for help.
postgres=# create table test_kenyon(id int,vname text);
CREATE TABLE
postgres=# insert into test_kenyon select generate_series(1,20),'kenyon good boy'||generate_series(1,20);
INSERT 0 20
postgres=# select * from test_kenyon ;
id | vname
----+-------------------
1 | kenyon good boy1
2 | kenyon good boy2
3 | kenyon good boy3
4 | kenyon good boy4
5 | kenyon good boy5
6 | kenyon good boy6
7 | kenyon good boy7
8 | kenyon good boy8
9 | kenyon good boy9
10 | kenyon good boy10
11 | kenyon good boy11
12 | kenyon good boy12
13 | kenyon good boy13
14 | kenyon good boy14
15 | kenyon good boy15
16 | kenyon good boy16
17 | kenyon good boy17
18 | kenyon good boy18
19 | kenyon good boy19
20 | kenyon good boy20
(20 rows)
2.创建物化视图
postgres=# create materialized view mv_test_kenyon as select * from test_kenyon where id > 10;
SELECT 10
postgres=# select * from mv_test_kenyon;
id | vname
----+-------------------
11 | kenyon good boy11
12 | kenyon good boy12
13 | kenyon good boy13
14 | kenyon good boy14
15 | kenyon good boy15
16 | kenyon good boy16
17 | kenyon good boy17
18 | kenyon good boy18
19 | kenyon good boy19
20 | kenyon good boy20
(10 rows)
postgres=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------+-------------------+----------+-------+-------------
public | mv_test_kenyon | materialized view | postgres | 16 kB |
public | test_kenyon | table | postgres | 16 kB |
(2 rows)
postgres=# \d mv_test_kenyon
Materialized view "public.mv_test_kenyon"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
vname | text |
--size有大小(默认空表是8kb,而这里是16kb)说明存储了数据，有相应的物理文件，并且有类似表的结构
--表和物化视图的文件地址
postgres=# select oid,pg_relation_filepath(oid),relpages from pg_class where relname = 'test_kenyon';
oid | pg_relation_filepath | relpages
-------+----------------------+----------
16396 | base/12896/16428 | 0
(1 row)
postgres=# select oid,pg_relation_filepath(oid),relpages from pg_class where relname = 'mv_test_kenyon';
oid | pg_relation_filepath | relpages
-------+----------------------+----------
16459 | base/12896/16459 | 0
(1 row)
3.物化视图更新
postgres=# insert into test_kenyon values(21,'bad boy');
INSERT 0 1
postgres=# insert into test_kenyon v