Postgresql ビューでdblinkを使用
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(列名 型名 [, ..]);
最初の方法でビュー定義しただけですねw
これだけでかなり楽になります。
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.列名1; col2 := rec.列名2; [..] 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のオプショナルインストールみたいなもの
*3:参考:http://database-tearoom.seesaa.net/article/25306577.html