PostgresDBlink 数据库跨库查询

梦里梦外; 2022-06-16 07:11 349阅读 0赞

Postgres 数据库跨库查询  
  
  
1 windows下，首先执行下面的sql，创建连接  
  
  
create extension dblink  
  
  
2 执行dblink\_connect保持连接  
  
  
Select dblink\_connect('test','host=localhost dbname=cbe\_sta user=postgres password=lifc126820');  
  
  
3 查看连接  
  
  
select dblink\_get\_connections()  
  
  
4 断开所有连接  
  
  
select dblink\_disconnect()  
  
  
5 断开指定名称的连接  
  
  
select dblink\_disconnect('test')  
  
  
注：如之前建立过名为‘test’的连接  
6 测试效果  
  
  
  
  
7 代码  
  
  
 SELECT t.pfr\_uuid,t.pfr\_line\_name,t.pfr\_line\_uuid,t.pfr\_line\_type,t.pfr\_station\_seq,t.pfr\_line\_station\_uuid,  
    t.pfr\_lng\_lat,  
    t.pfr\_open\_door\_time,  
    t.pfr\_close\_door\_time,  
    t.pfr\_get\_on\_number,  
    t.pfr\_get\_off\_number,  
    t.pfr\_upload\_time,  
    t.pfr\_pfod\_uuid,  
    t.prf\_enter\_out\_status,  
    t.prf\_dev\_uuid,  
    t.prf\_dev\_code,  
    t.prf\_get\_f\_on\_number,  
    t.prf\_get\_f\_off\_number,  
    t.prf\_get\_c\_on\_number,  
    t.prf\_get\_c\_off\_number,  
    t.prf\_get\_e\_on\_number,  
    t.prf\_get\_e\_off\_number,  
    t.prf\_dev\_datastring,  
    t.prf\_get\_person\_count,  
    t.pfr\_station\_uuid  
   FROM dblink('host=localhost dbname=scp\_b user=postgres password=postgres'::text, 'select pfr\_uuid,pfr\_line\_name,pfr\_line\_uuid,pfr\_line\_type,pfr\_station\_seq,pfr\_line\_station\_uuid,  
pfr\_lng\_lat,pfr\_open\_door\_time,pfr\_close\_door\_time,pfr\_get\_on\_number,pfr\_get\_off\_number,  
pfr\_upload\_time,pfr\_pfod\_uuid,prf\_enter\_out\_status,prf\_dev\_uuid,prf\_dev\_code,prf\_get\_f\_on\_number,prf\_get\_f\_off\_number,  
prf\_get\_c\_on\_number,prf\_get\_c\_off\_number,prf\_get\_e\_on\_number,prf\_get\_e\_off\_number,prf\_dev\_datastring,prf\_get\_person\_count, pfr\_station\_uuid from t\_passenger\_flow\_result'::text) t(pfr\_uuid character varying(20), pfr\_line\_name character varying(50), pfr\_line\_uuid character varying(20), pfr\_line\_type character varying(1), pfr\_station\_seq character varying(10), pfr\_line\_station\_uuid character varying(20), pfr\_lng\_lat character varying(100), pfr\_open\_door\_time character varying(20), pfr\_close\_door\_time character varying(20), pfr\_get\_on\_number integer, pfr\_get\_off\_number integer, pfr\_upload\_time timestamp(6) without time zone, pfr\_pfod\_uuid character varying(20), prf\_enter\_out\_status character varying(1), prf\_dev\_uuid character varying(20), prf\_dev\_code character varying(10), prf\_get\_f\_on\_number integer, prf\_get\_f\_off\_number integer, prf\_get\_c\_on\_number integer, prf\_get\_c\_off\_number integer, prf\_get\_e\_on\_number integer, prf\_get\_e\_off\_number integer, prf\_dev\_datastring character varying(500), prf\_get\_person\_count integer, pfr\_station\_uuid character varying(20));