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

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

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

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

 dblink

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

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

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

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

テーブル

データベース名テーブル名
db1table1
db2table2

table1のフィールド

フィールド名
db1_field1int
db1_field2text
db1_field3timestamp

table2のフィールド

フィールド名
db2_field1int
db2_field2text
db2_field3timestamp

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

ログインする

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

記事を共有

最近人気な記事