postgresqlでデータベース間でテーブル結合する方法(dblink)

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

postgresqlでデータベース間でテーブル結合する方法を紹介します。

■ dblink

contlibにdblinkというモジュールが登録されているのでこれを利用する。

windowsのインストーラーからインストールした場合同梱されています。

以下のデータベースを例に記載します

データベース		host名
db1	カレントのデータベース	host1
db2	接続先のデータベース	host2

テーブル

データベース名	テーブル名
db1	table1
db2	table2

table1のフィールド

フィールド名	型
db1_field1	int
db1_field2	text
db1_field3	timestamp

table2のフィールド

フィールド名	型
db2_field1	int
db2_field2	text
db2_field3	timestamp

■ データベースにモジュールを適用する。

ログインする

psql -U [ユーザ―名] -d [モジュールを適用するデータベース名]

モジュールをインストール

create extension dblink;

これでインストールされます。

■ 接続

以下のSQLを発行すると’接続名’で定義した名前でコネクションを生成します。

select dblink_connetct(‘接続名’,’host=host2 port=5432 dbname=db2 user=xxx password=xxx’);

■ 切断

以下のSQLを発行すると’接続名’で定義したコネクションを切断します。

select dblink_disconnect(‘接続名’);

■ 接続先のデータベースにSELECT文を実行するには

上記の接続を実行後に以下のSQL実行することで、値が取得出来ます。

※SQLの返り値の型指定をしないとけないのが面倒。

select *

from dblink(‘接続名’,’ select db2_field1, db2_field2, db2_field3 from table2 ‘)

as t1 ( db2_field1 int, db2_field2 text, db2_field3 timestamp)

■ このままでは、SQLが複雑になるのでビューにするとよい。

以下のようにビューを作ると。。。

create view db2_view as

select *

from dblink(‘接続名’,’ select db2_field1, db2_field2, db2_field3 from table2 ‘)

as t1 ( db2_field1 int, db2_field2 text, db2_field3 timestamp);

通常のテーブルのようにSQLが発行出来る。

select * from db2_view;

カレントデータベースにあるテーブルと結合してみる。

普通の結合文のように実行すれば結果が取得出来ます。

select db1.*, db2_view.* from db1

join db2_view on db2_view.db2_field1 = db1.db1_field1;

このように、データベースが異なる場合でもテーブル結合ができるので

活用してみてはいかがでしょうか？

詳細は、PostgreSQLのドキュメント、付録 F.追加で提供されるモジュールの項のdblinkに詳しく書いてあります。

http://www.postgresql.jp/document/9.1/html/dblink.html