[[193346]]

這篇文章主要介紹了PostgreSQL中使用dblink實(shí)現(xiàn)跨庫(kù)查詢的方法,需要的朋友可以參考下。

最近一個(gè)sql語(yǔ)句涉及到跨庫(kù)的表之間的聯(lián)合查詢，故研究學(xué)習(xí)下。

一開(kāi)始研究知道了sql語(yǔ)句的寫(xiě)法，但是執(zhí)行通過(guò)不了，需要先安裝dblink擴(kuò)展。這些博文里都沒(méi)說(shuō)清楚，感謝網(wǎng)友指點(diǎn)，在windows下只需要在相應(yīng)的數(shù)據(jù)庫(kù)下執(zhí)行sql語(yǔ)句“create extension dblink”就ok了。而以上的博文基本上說(shuō)的都是linux下的操作方法，也因此我走了彎路。

2.下載了PostgreSQL的源代碼，卻不知道語(yǔ)句在哪執(zhí)行，貌似是在linux下的shell里執(zhí)行的，我卻在psql和cmd里都試過(guò)，都是走過(guò)的彎路啊。也嘗試過(guò)直接拷貝dblink.sql里的語(yǔ)句運(yùn)行，卻通不過(guò)報(bào)錯(cuò)。

3.windows下很簡(jiǎn)單，一句sql就搞定了“create extension dblink”.

在擴(kuò)展里可以看到多了一個(gè)dblink：

函數(shù)里也相應(yīng)的有了dblink開(kāi)頭的一些函數(shù)：

這句sql語(yǔ)句貌似只調(diào)用運(yùn)行了dblink.control文件：

3.寫(xiě)sql語(yǔ)句測(cè)試下，是否ok。

跨庫(kù)查詢，先要建立數(shù)據(jù)庫(kù)連接，才能查詢否則會(huì)報(bào)錯(cuò)。

如圖是提示的connection named"unnamed",因?yàn)橛衅渌倪B接，如果沒(méi)有任何連接，則會(huì)提示connection not available：

使用dblink(text,text)，***個(gè)參數(shù)是連接串，第二個(gè)參數(shù)是sql語(yǔ)句。執(zhí)行成功：

或者使用dblink_connect(text)先建立連接，再用dblink(text)做跨庫(kù)查詢：

查看連接：

select dblink_get_connections() 

斷開(kāi)所有連接：

select dblink_disconnect() 

斷開(kāi)指定名稱的連接：

select dblink_disconnect('test') 

如之前建立過(guò)名為‘test'的連接：

select dblink_connect('test','host=localhost dbname=cbe_sta user=postgres password=lifc126820'); 

則會(huì)提示斷開(kāi)成功。

4.***，我自己需要的sql語(yǔ)句也ok了，在同一個(gè)服務(wù)器上的兩個(gè)數(shù)據(jù)庫(kù)3張表的聯(lián)合查詢，先建立連接再做查詢：

select dblink_connect('host=localhost dbname=cbe_userdata user=postgres password=lifc126820');  
   
select A.id,A.code as poicode,A.cname,A.geo,A.x,A.y,A.s01,A.s02,A.s03,A.updatetime,A.tbcode,D.code,D.data,D.value,D.cname as colname,D.ifdata,D.sort from tb_test_poi A inner join ((select * from dblink('select poicode,code,data,value from tb_test_data_poi') as T1(poicode character varying(50),code character varying(50),data double precision,value character varying(500))) B inner join (select * from dblink('select cname,code as code1,ifdata,sort from tb_test_index_poi') as T2(cname character varying(200),code1 character varying(50),ifdata character varying(5),sort character varying(50))) C on B.code=C.code1) D on A.code=D.poicode; 

如果做成視圖也行，這里就需要用dblink(text,text)，如果直接用上面的sql語(yǔ)句會(huì)報(bào)錯(cuò)說(shuō)無(wú)法建立連接：

CREATE OR REPLACE VIEW vw_test_poi AS 
 SELECT a.id, a.code AS poicode, a.cname, a.geo, a.x, a.y, a.s01, a.s02, a.s03,  
  a.updatetime, a.tbcode, d.code, d.data, d.value, d.cname AS colname,  
  d.ifdata, d.sort  
  FROM tb_test_poi a  
  JOIN (( SELECT t1.poicode, t1.code, t1.data, t1.value  
      FROM dblink('host=localhost dbname=cbe_userdata user=postgres password=lifc126820'::text, 'select poicode,code,data,value from tb_test_data_poi'::text) t1(poicode character varying(50), code character varying(50), data double precision, value character varying(500))) b  
  JOIN ( SELECT t2.cname, t2.code1, t2.ifdata, t2.sort  
      FROM dblink('host=localhost dbname=cbe_userdata user=postgres password=lifc126820'::text, 'select cname,code as code1,ifdata,sort from tb_test_index_poi'::text) t2(cname character varying(200), code1 character varying(50), ifdata character varying(5), sort character varying(50))) c ON b.code::text = c.code1::text) d ON a.code::text = d.poicode::text 

我發(fā)覺(jué)執(zhí)行效率有點(diǎn)慢啊，查出來(lái)的數(shù)據(jù)是1萬(wàn)多條，用了2秒多。目前我的程序里并沒(méi)有用dblink來(lái)做，因?yàn)檩斎雲(yún)?shù)可以先單獨(dú)查一張表即可，然后給另外兩張表的信息關(guān)聯(lián)查詢上ok了，所以我分兩步來(lái)做了，解決了這個(gè)問(wèn)題。具體效率未深入對(duì)比，如果后期需要調(diào)整，可以考慮用dblink來(lái)做，這樣畢竟程序里代碼會(huì)簡(jiǎn)單很多。