Postgresql ビューでdblinkを使用

Postgres データベース

postgresqlでカレントデータベースから他のデータベースに接続する場合は
dblinkというcontrib*1をインストールして実現できるのですが、
そのまま使おうとすると非常に使いづらい…*2
なわけで、よく使うテーブルをあらかじめビューにしておくと良いのでは？
と思い調査してみました。

まずはdblinkのインストール*3

# cd /usr/local/src/postgresql-8???/contrib/dblink
# make
# make install
# psql -U postgres -d データベース名 < dblink.sql

これだけでOK。
ちなみに、リンク元のデータベースだけにインストールすればいいみたい。

dblinkを使ってみる

単純に使いたいなら以下の方法（もちろん、hostやportも指定可能）。

select * from 
 dblink(
  'dbname=データベース user=ユーザ password=パスワード',
  'select 列名 [, ..] from テーブル [where ..]')
 as t1(列名 型名 [, ..]);

このように、SELECTする列をいちいち指定しないといけないのがかなり面倒。
「無指定のときは全部TEXTで見る」のようにしてくれたらだいぶ楽になるのになー。


接続識別子を使いまわしたいなら以下の方法。

select dblink_connect('接続識別子','dbname=データベース user=ユーザ password=パスワード');

select * from 
 dblink(
  '接続識別子',
  'select 列名 [, ..] from テーブル [where..]')
 as t1(列名 型名 [, ..]);

select dblink_disconnect('接続識別子','dbname=データベース');

非スーパーユーザで「ユーザ／パス」を省略したい場合は
dblink_connect_u で接続すると良い。要EXECUTE権限。


あと、「insert」「update」「delete」など更新系のsqlを投げる場合は
dblink_exec や dblink_build_sql_???? を使用すればOKです。

ビューにしてみる

上記のように、毎回上記のようなことをやるのは非常に面倒。
なのでビューにしてみました。

create view myremote_pg_proc as
 select * from 
  dblink(
   'dbname=データベース user=ユーザ password=パスワード',
   'select 列名 [, ..] from テーブル  [where..]')
  as t1(列名 型名 [, ..]);

最初の方法でビュー定義しただけですねｗ
これだけでかなり楽になります。

pl/pgsqlにしてみる

dblinkはリモート側の問い合わせ結果をローカルシステム側に渡す前にすべて取り込みます。問い合わせが大量の行を返すと想定される場合、dblink_openでカーソルを開き、一度に管理可能な行数を取り出す方が良いでしょう。

PostgreSQL日本語ドキュメント　dbkink

大量データを取ってくる場合は、カーソルを使った方が良さそうです。
カーソルを使う場合のストアドを書いてみました。

create or replace function myremote_plpgsql(
 OUT col1 text,
 OUT col2 text,
 [..]
)
RETURNS SETOF RECORD AS $$
DECLARE
  rec record;
  dmy  text;
  connect_string  text := '接続識別子';
  cursor_string  text  := 'カーソル名';
BEGIN

  select dblink_connect(connect_string,'dbname=データベース user=ユーザ password=パスワード') into dmy;
  select dblink_open(connect_string,cursor_string,'select 列名 [, ..] from テーブル名 [where..]') into dmy;

  LOOP
    select *
     into rec
      from dblink_fetch(connect_string,cursor_string,1) as (列名 型名 [, ..]);
    IF NOT FOUND THEN EXIT; END IF;
    col1 := rec.列名１;
    col2 := rec.列名２;
    [..]
    RETURN NEXT;
  END LOOP;
  
  select dblink_close(connect_string, cursor_string) into dmy;
  select dblink_disconnect(connect_string) into dmy;

  EXCEPTION
    WHEN OTHERS THEN
      select dblink_disconnect(connect_string) into dmy;  RETURN;
END;
$$ LANGUAGE plpgsql;

全部のストアドに「ユーザ／パス」を書くのはよろしくないので、
dblink_connect_u で接続した方が望ましいでしょう。

（dblinkドキュメント）
http://www.postgresql.jp/document/pg835doc/html/dblink.html

*1:postgresのオプショナルインストールみたいなもの

*2:Oracleは@DB名だけでOK

*3:参考：http://database-tearoom.seesaa.net/article/25306577.html