psql物化视图自动更新

更新物化视图示例

CREATE TABLE model.test (  id SERIAL PRIMARY KEY,  name VARCHAR(60));

. 创建物化视图

CREATE MATERIALIZED VIEW model.test_materialized_view AS  SELECT * FROM model.test;

. 创建唯一索引，因为 CONCURRENTLY 同步更新需要唯一索引

CREATE UNIQUE INDEX test_materialized_view_index ON model.test_materialized_view(id);

. 同步更新，但是需要触发，不能向 oracle 那种可以自动触发更新

REFRESH MATERIALIZED VIEW CONCURRENTLY model.test_materialized_view;

. 创建函数执行同步更新和触发器执行函数

create or replace function update_test_materialized_view() returns trigger as $$declarebegin  REFRESH MATERIALIZED VIEW CONCURRENTLY model.test_materialized_view;  return new;end;$$ language plpgsql;CREATE TRIGGER update_test_materialized_view AFTER UPDATE ON model.test FOR EACH STATEMENT EXECUTE PROCEDURE  update_test_materialized_view();CREATE TRIGGER update_test_materialized_view AFTER UPDATE ON model.test FOR EACH STATEMENT EXECUTE PROCEDURE  update_test_materialized_view();CREATE TRIGGER update_test_materialized_view AFTER UPDATE ON model.test FOR EACH STATEMENT EXECUTE PROCEDURE  update_test_materialized_view();