postgres fdw 物化视图 使用

-- 创建拓展
create extension postgres_fdw;
drop SERVER postgres CASCADE; --递归删除 会删除已经通过fdw创建的表
-- 创建一个server name  postgres
create server postgres foreign data wrapper postgres_fdw options (host '127.0.0.1', port '5432', dbname 'postgres');
create user mapping for public server postgres options (user 'postgres', password 'postgres');

-- 基于server name 创建关联表 替代dblink (可以理解为视图)
create foreign table  tablename11( czrk numeric, xzqdm character varying, shengming character varying, shiming character varying, xianming character varying, xiangzhen character varying, dzxiang character varying, gid numeric)
server postgres options (schema_name 'riskevaluate', table_name 'tablename');

　　

-- 查询 fdw server
select * from pg_foreign_server;

　　

 

物化视图

--创建物化视图
create materialized view view_name as query_sql;
--刷新物化视图数据
refresh materialized view view_name;