PostgresDBlink 数据库跨库查询

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));