Postgres -- 创建实体化视图 & 刷新实体化视图 & 删除实体化视图

创建视图

可以将若干表中的信息汇聚到一张视图中，成为一张"表"，方便查询，提高查询效率。在不刷新视图的情况下，视图中的内容不会改变

CREATE MATERIALIZED VIEW PUBLIC."acl_test_view$v" TABLESPACE pg_default AS 
	SELECT
	r.ID,
	r.NAME,
	r.type_id,
	TYPE.category 
	FROM
		acl_inventory_resource r
		LEFT JOIN acl_inventory_res_type TYPE ON r.type_id = TYPE.ID 
WITH DATA;

创建索引

CREATE UNIQUE INDEX test_view_index
    ON public."acl_test_view$v" USING btree
    (id COLLATE pg_catalog."default", name COLLATE pg_catalog."default")
    TABLESPACE pg_default;

如果不加索引，刷新视图如果带 CONCURRENTLY 就会报错，报错如下：

REFRESH MATERIALIZED VIEW CONCURRENTLY PUBLIC."acl_test_view$v"
> ERROR:  cannot refresh materialized view "public."acl_test_view$v"" concurrently
  HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

CONCURRENTLY 非阻塞

查询视图

SELECT
	* 
FROM
	acl_test_view$v;

刷新视图

REFRESH MATERIALIZED VIEW CONCURRENTLY PUBLIC."acl_test_view$v";

删除视图

DROP MATERIALIZED VIEW PUBLIC."acl_test_view$v" ;