2006年10月12日

PostgreSQLのdblink(database link)


PostgreSQLで他のPostgreSQLデータベースのデータにアクセスするには、contribで提供されているdblinkを利用します。
これの利用方法についての簡単なメモです。
ここに登場していない関数や説明もありますが、それは今後完全版をDatabase tearoomの方に書きたいと思っています。

● 環境構築
UNIXやLinuxでは、ソースからコンパイルし、インストールします。(rpmなどのパッケージでインストールしている場合はcontribのパッケージをインストールして、dblink.sqlを実行すればいいんだと思う。たぶん…^^;)
dblinkのソースのディレクトリ(ex. /usr/local/src/postgresql-?.?.?/contrib/dblink)で実施
# make
# make install
$ psql -U postgres -d {DBNAME} < dblink.sql
適切なOSユーザーで実施してください(丁寧な説明でなくてスマン^^;)。
{DBNAME}はdblinkを利用したいデータベース名。これをtemplate1とすることで今後作成するデータベースで利用できるようになる。

Windowsでは、インストーラを利用してインストールした場合、dblink.sqlを実行するだけでOKです。
このファイルはPostgreSQLをインストールしたフォルダの下のshare\contribにあります。
C:\Program Files\PostgreSQL\8.1> bin\psql -U postgres -d {DBNAME} < share\contrib\dblink.sql
{DBNAME}はdblinkを利用したいデータベース名。これをtemplate1とすることで今後作成するデータベースで利用できるようになる。

● 使用方法
"[" と "]" で括られている接続識別名は省略可能です。ただし接続識別名を接続時に指定した場合は常に必要です。※ "[" および "]" は記述不要。

  • アクセスしたいテーブルなどがあるデータベースに接続
    select dblink_connect(['{接続識別名}', ]'{接続文字列}');
    {接続文字列}にはlibpqのPQconnectdb関数で利用するキーワードを記述します。
    参照.) PostgreSQLマニュアル - libpq - Cライブラリ - データベース接続制御関数
    ex.)
    select dblink_connect('conn1',
                          'host=dbsv port=5432 dbname=mydb user=scott password=tiger');
  • 検索(SELECT)
    select *
      from dblink(['{接続識別名}', ]
                  'select {列名}[, {列名}...]
                   from {オブジェクト名}')
               t1({列名} {データ型}[, {列名} {データ型}...]);
    ex.)
    select *
     from dblink('conn1',
                  'select oid, relname
                     from pg_class')
             t1(oid oid, relname text);
  • 更新(UPDATE/INSERT/DELETE)
    select dblink_exec(['{接続識別名}', ]{DML文}[, fail_on_error]);
    ex.)
    select dblink_exec('conn1',
                       'delete from emp where empno=7800');
  • データベースから切断
    select dblink_disconnect(['{接続識別名}']); ex.)
    select dblink_disconnect('conn1');
2006/10/13 Windows環境での構築方法を追記
2006/10/13 UNIX/Linux環境で構築する際に適切なOSユーザーで実行する必要があることと、rpmなどのパッケージでインストールしている場合のコメントを(かなりテキトーに^^;)追記

<<<押して〜
 
posted by kunyami at 10:52 | Comment(10) | TrackBack(0) | PostgreSQL
この記事へのコメント
dblinkで悩んでいたところに、このページ大変役立ってます。
とはいえ、TeraTarmでやってみるとうまくいくのですが、pl/pgSQLで実行したいのです。
同じようにすると、エラーとなってしまいます。
何か、書き方が違うのか、設定がいるのでしょうか?

また、取得したデータを参照しながら元のDBを更新するようになるのですが、参照の仕方はどうなるのでしょうか?fetch?これも使い方に悩んでおります。

Posted by miumiu at 2006年11月01日 15:15
とりあえずPL/PgSQLでdblink関数を使うにはこんな感じです。
PL/PgSQLだとdblink関連関数呼び出しのselect文にもinto句が必要なのがミソですかね。
※コメント欄はフォントの制御とかできないみたいで見づらくてすいません。
-----
create or replace function linkfunc() returns text as $$
declare
    r1  text;
    ret text;
begin
    select dblink_connect('conn1',
                          'host=dbsv
                           port=5432
                           dbname=mydb
                           user=scott
                           password=tiger')
      into r1;
    select *
     into ret
      from dblink('conn1',
                  $x$select current_database() || ':' || relname as a
                     from pg_class limit 1$x$)
             t1(a text);
    select dblink_disconnect('conn1')
      into r1;
    return ret;
end;
$$
language plpgsql;
-----
fetchについては後ほど。

Posted by kunyami at 2006年11月01日 16:29
fetchの前に…以下がやりたいことにマッチしませんか?
-----
create table test_link (a text, b text);

create or replace function linkfunc2() returns text as $$
declare
    r1  text;
    ret text;
    rec record;
begin
    select dblink_connect('conn1',
                          'host=dbsv
                           port=5432
                           dbname=mydb
                           user=scott
                           password=tiger')
      into r1;
    for rec in select *
                 from dblink('conn1',
                             'select current_database() as a, relname as b
                                from pg_class limit 10')
                        t1(a text, b text)
    loop
        insert into test_link values (rec.a, rec.b);
    end loop;
    select dblink_disconnect('conn1')
      into r1;
    return r1;
end;
$$
language plpgsql;
-----

Posted by kunyami at 2006年11月01日 20:16
上記と同じことをdblink_open/dblink_fetchを利用するなら下記のような感じでしょうか。
-----
create or replace function linkfunc3() returns text as $$
declare
    r1  text;
    ret text;
    rec record;
begin
    select dblink_connect('conn1',
                          'host=dbsv
                           port=5432
                           dbname=mydb
                           user=scott
                           password=tiger')
      into r1;
    select dblink_open('conn1',
                       'cur1',
                       'select current_database() as a, relname as b
                          from pg_class limit 10')
      into r1;
    loop
        select *
          into rec
          from dblink_fetch('conn1',
                            'cur1',
                            1) as (a text, b text);
        if not found then
            exit;
        end if;
        insert into test_link values (rec.a, rec.b);
    end loop;
    select dblink_close('conn1', 'cur1')
      into r1;
    
    select dblink_disconnect('conn1')
      into r1;
    return ret;
end;
$$
language plpgsql;
-----

Posted by kunyami at 2006年11月01日 20:32
最初のコメントに「into句がミソ」と書きましたが、perform文を利用する手もありますね。(これだとエラーハンドリングができなくなるけど。)

Posted by kunyami at 2006年11月01日 20:36
はじめまして。dblink初心者です。
dblinkを使用してINSERTやUPDATEを行って
おりますが、TEXTフィールドに挿入する
データに「'」(シングルクォート)が含まれている
場合の処理がうまく動作しません。

例えば、

select dblink_exec('conn1', 'insert into emp(name) values (\'AAAAAA\')');

だと正常に動作しますが、AAAAAA に「'」が
含まれている場合、どのようにエスケープすれば
よいのかがわかりません。

色々と試したのですが解決方法が見つからず
こちらに書き込ませていただきました。


Posted by 盛川 徹 at 2009年09月08日 11:09
> 盛川さん
dblinkで無い場合は「''」のようにシングルコートを二つ並べることで「'」を表現しますよね。
なのでこの「''」も「\'\'」として\エスケープしてあげればよいのです。
ex.) select dblink_exec('conn1', 'insert into emp(name) values (\'AAA\'\'AAA\')');

あるいは、dblinkでなければ、文字列リテラルの前後を「$$」(${任意の文字列}$)で囲むことで、シングルコートを\エスケープしないで書くことができます。
ex.)
insert into emp(name) values($$AAA'AAA$$);
insert into emp(name) values($abc$AAA'AAA$abc$);

「$$」を利用する場合はdblinkでは文字列内のシングルコートを\エスケープするだけで済みます。
ex.)
select dblink_exec('conn1', 'insert into emp(name) values ($$AAA\'AAA$$)');
select dblink_exec('conn1', 'insert into emp(name) values ($abc$AAA\'AAA$abc$)');

これでいかがでしょうか?

Posted by kunyami at 2009年09月08日 15:27
DBリンクはできたんですが、
あるテーブルに更新があった場合に、別DBにある
テーブルを更新したくて
トリガーを作ったのですがうまくいきません。
トリガーから呼んだファンクションで
select dblink_connect(##,###)
をどう実行しても。。

ERROR: query has no destination for result data

になってしまいます。
助けてください。

Posted by arai at 2009年09月08日 22:21
> araiさん
試しに以下のようにやってみたところ、問題なく別DBにあるテーブルも更新できました。
-----
DBリンク元のデータベースでテーブル、ファンクション、トリガを作成
create table src(col01 numeric, col02 varchar(50));
create function func_linktest() returns trigger
  as $$
     declare
         r1  text;
         ret text;
     begin
         select dblink_connect('conn1', 'dbname=kuniteru')
           into r1;
         select dblink_exec('conn1', 'insert into dest values (' || new.col01 || ', ''' || new.col02 || ''')')
           into ret;
         select dblink_disconnect('conn1')
           into r1;
         return new;
     end;
     $$
  language plpgsql;

create trigger trg_linktest
  before insert
  on src for each row
  execute procedure func_linktest();

DBリンク先のデータベースでテーブルを作成
create table dest(col01 numeric, col02 varchar(50));

DBリンク元のデータベースでデータをインサート
insert into src values (1, 'A');
-----
ご参考まで。

Posted by kunyami at 2009年09月08日 23:59
C:\Program Files\PostgreSQL\8.4>bin\psql -U postgres -d XXXX < share\contrib\dblink.sql

これってかなり Oracle みたいな感じですね。
でも sys か system とかで悩まないだけいいです。

Posted by 田中 at 2010年02月16日 14:55
コメントを書く
お名前: [必須入力]

メールアドレス:

ホームページアドレス:

コメント: [必須入力]

※ブログオーナーが承認したコメントのみ表示されます。
この記事へのトラックバックURL
http://blog.seesaa.jp/tb/25306577
※ブログオーナーが承認したトラックバックのみ表示されます。

この記事へのトラックバック
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。