目次
この記事はアピリッツの技術ブログ「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に詳しく書いてあります。
