[Postgresql] 用 dblink 做跨資料庫查詢

dblink 是一個Postgresql的模組，透過dblink，你可以做跨資料庫查詢

（select, insert , join）等等 ，它預設是沒有安裝的，

每一個資料庫第一次使用都必須手動安裝。

---

安裝方式

CREATE EXTENSION dblink

安裝好後可以看到Extension中多出了dblink，表示可以使用了。

dblinke有以下function可以使用 － 資料來源

dblink_connect -- opens a persistent connection to a remote database

dblink_connect_u -- opens a persistent connection to a remote database, insecurely

dblink_disconnect -- closes a persistent connection to a remote database

dblink -- executes a query in a remote database

dblink_exec -- executes a command in a remote database

dblink_open -- opens a cursor in a remote database

dblink_fetch -- returns rows from an open cursor in a remote database

dblink_close -- closes a cursor in a remote database

dblink_get_connections -- returns the names of all open named dblink connections

dblink_error_message -- gets last error message on the named connection

dblink_send_query -- sends an async query to a remote database

dblink_is_busy -- checks if connection is busy with an async query

dblink_get_notify -- retrieve async notifications on a connection

dblink_get_result -- gets an async query result

dblink_cancel_query -- cancels any active query on the named connection

dblink_get_pkey -- returns the positions and field names of a relation's primary key fields

dblink_build_sql_insert --  builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values

dblink_build_sql_delete -- builds a DELETE statement using supplied values for primary key field values

dblink_build_sql_update -- builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values

---

在這裡只簡單介紹自己最常用的 dblink  這個function ，如何查詢遠端資料，

與遠端資料庫做Join ，如果有興趣，可以參考這個網頁 。


首先我有2個資料庫，都有相同的資料表結構，但有不同的資料。

store_a 與 store_b

我們先來看一下store_a 以及 store_b 的資料表

接下來是資料

store_a   user_basi

stroe_a user_login

store_b  user_basic

store_b user_login

接下來，我們在store_a 資料庫下如下的SQL，測試一下是否可以連線

 select dblink_connect('hostaddr=127.0.0.1 port=5432 dbname=store_b user=postgres password=mypwd');  

如果可以正常連線，則會顯示如下訊息

若無法連線則會顯示(包含使用者帳、密打錯也是如此，用在程式中要注意Exception)

我們在store_a 資料庫想要查詢store_b的user_basic則如此寫

 SELECT id,name,year  
   FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=store_b user=postgres password=mypwd', 'SELECT id,name,year  
  FROM user_basic;')  
    AS t1(id integer ,name character varying(10) ,year integer) ;  

執行結果，的確查到了store_b  的user_basic資料表

接下來我要把二個資料資的表做Join，找出二個資料表中都有的人名

 SELECT user_basci.name,user_basci.year  
   FROM user_basci LEFT JOIN dblink('hostaddr=127.0.0.1 port=5432 dbname=store_b user=postgres password=mypwd', 'SELECT name,year  
  FROM user_basic;')  
    AS t1(name character varying(10) ,year integer) on user_basci.name=t1.name where length(t1.name)>0;  

執行結果，找到2邊都有的bert。

今天我示範的是在同一個Postgresql Server內的不同資料庫做查詢，其實是可以跨Server做查詢

的，只要把IP的部份由 127.0.0.1 改成 另一個資料庫所在的IP即可。

以上簡單紀錄一個使用dblink的心得，新年快樂。