PostgreSQL中使用dblink實現(xiàn)跨庫查詢的方法
最近一個sql語句涉及到跨庫的表之間的聯(lián)合查詢，故研究學習下。
一開始研究知道了sql語句的寫法，但是執(zhí)行通過不了，需要先安裝dblink擴展。這些博文里都沒說清楚，感謝網(wǎng)友指點，在windows下只需要在相應的數(shù)據(jù)庫下執(zhí)行sql語句“create extension dblink”就ok了。而以上的博文基本上說的都是linux下的操作方法，也因此我走了彎路。
2.下載了PostgreSQL的源代碼，卻不知道語句在哪執(zhí)行，貌似是在linux下的shell里執(zhí)行的，我卻在psql和cmd里都試過，都是走過的彎路啊。也嘗試過直接拷貝dblink.sql里的語句運行，卻通不過報錯。
3.windows下很簡單，一句sql就搞定了“create extension dblink”.
在擴展里可以看到多了一個dblink：
函數(shù)里也相應的有了dblink開頭的一些函數(shù)：
這句sql語句貌似只調(diào)用運行了dblink.control文件：
3.寫sql語句測試下，是否ok。
跨庫查詢，先要建立數(shù)據(jù)庫連接，才能查詢否則會報錯。
如圖是提示的connection named"unnamed",因為有其他的連接，如果沒有任何連接，則會提示connection not available：
使用dblink(text,text)，第一個參數(shù)是連接串，第二個參數(shù)是sql語句。執(zhí)行成功：
或者使用dblink_connect(text)先建立連接，再用dblink(text)做跨庫查詢：
查看連接：
select dblink_get_connections()
斷開所有連接：
select dblink_disconnect()
斷開指定名稱的連接：
select dblink_disconnect('test')
如之前建立過名為‘test'的連接：
select dblink_connect('test','host=localhost dbname=cbe_sta user=postgres password=lifc126820');
則會提示斷開成功。
4.最后，我自己需要的sql語句也ok了，在同一個服務器上的兩個數(shù)據(jù)庫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語句會報錯說無法建立連接：
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ā)覺執(zhí)行效率有點慢啊，查出來的數(shù)據(jù)是1萬多條，用了2秒多。目前我的程序里并沒有用dblink來做，因為輸入?yún)?shù)可以先單獨查一張表即可，然后給另外兩張表的信息關聯(lián)查詢上ok了，所以我分兩步來做了，解決了這個問題。具體效率未深入對比，如果后期需要調(diào)整，可以考慮用dblink來做，這樣畢竟程序里代碼會簡單很多。
相關文章
這篇文章主要介紹了PostgreSQL?DBA最常用SQL?,主要包括背景及常用查詢語句，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-10-10
這篇文章主要介紹了PostgreSQL 允許遠程訪問設置的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
最近有這么一個工程,需要使用postgresql數(shù)據(jù)庫,在數(shù)據(jù)庫中的好幾個表中都需要時間戳這個字段,這篇文章主要給大家介紹了關于PostgreSQL自動更新時間戳的相關資料,需要的朋友可以參考下2021-11-11
這篇文章主要介紹了Postgresql分布式插件plproxy的使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL 實現(xiàn)定時job執(zhí)行(pgAgent)
這篇文章主要介紹了PostgreSQL 實現(xiàn)定時job執(zhí)行(pgAgent)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
自定義函數(shù)實現(xiàn)單詞排序并運用于PostgreSQL(實現(xiàn)代碼)
這篇文章主要介紹了自定義函數(shù)實現(xiàn)單詞排序并運用于PostgreSQL，本文給大家分享實現(xiàn)代碼，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-04-04
這篇文章主要介紹了postgresql 如何關閉自動提交的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL是一款簡介而又性能強大的數(shù)據(jù)庫應用程序,其在日期時間數(shù)據(jù)方面所支持的功能也都非常給力,下面這篇文章主要給大家介紹了關于PostgreSQL的日期時間差DATEDIFF的相關資料,需要的朋友可以參考下2023-04-04
PostgreSQL的整型、浮點型、固定精度數(shù)值和序列等數(shù)字類型
PostgreSQL（簡稱PGSQL）是一種開源關系型數(shù)據(jù)庫管理系統(tǒng),廣泛應用于企業(yè)級應用,文章詳細介紹了PostgreSQL的數(shù)字類型,包括整型、浮點型、固定精度數(shù)值型和序列類型,強調(diào)了選擇合適的數(shù)字類型對于數(shù)據(jù)庫的存儲效率、查詢性能和數(shù)據(jù)準確性的重要性2024-09-09
這篇文章主要介紹了淺談PostgreSQL的客戶端認證pg_hba.conf，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01