これの利用方法についての簡単なメモです。
ここに登場していない関数や説明もありますが、それは今後完全版を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
|
Windowsでは、インストーラを利用してインストールした場合、dblink.sqlを実行するだけでOKです。
このファイルはPostgreSQLをインストールしたフォルダの下のshare\contribにあります。
C:\Program Files\PostgreSQL\8.1> bin\psql -U postgres -d {DBNAME} < share\contrib\dblink.sql
|
● 使用方法
"[" と "]" で括られている接続識別名は省略可能です。ただし接続識別名を接続時に指定した場合は常に必要です。※ "[" および "]" は記述不要。
- アクセスしたいテーブルなどがあるデータベースに接続
select dblink_connect(['{接続識別名}', ]'{接続文字列}');
ex.)※ {接続文字列}にはlibpqのPQconnectdb関数で利用するキーワードを記述します。
参照.) PostgreSQLマニュアル - libpq - Cライブラリ - データベース接続制御関数select dblink_connect('conn1',
'host=dbsv port=5432 dbname=mydb user=scott password=tiger'); - 検索(SELECT)
ex.)select *
from dblink(['{接続識別名}', ]
'select {列名}[, {列名}...]
from {オブジェクト名}')
t1({列名} {データ型}[, {列名} {データ型}...]);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 UNIX/Linux環境で構築する際に適切なOSユーザーで実行する必要があることと、rpmなどのパッケージでインストールしている場合のコメントを（かなりテキトーに^^;）追記
とはいえ、TeraTarmでやってみるとうまくいくのですが、pl/pgSQLで実行したいのです。
同じようにすると、エラーとなってしまいます。
何か、書き方が違うのか、設定がいるのでしょうか？
また、取得したデータを参照しながら元のＤＢを更新するようになるのですが、参照の仕方はどうなるのでしょうか？fetch？これも使い方に悩んでおります。
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については後ほど。
-----
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;
-----
-----
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;
-----
dblinkを使用してINSERTやUPDATEを行って
おりますが、TEXTフィールドに挿入する
データに「'」(シングルクォート)が含まれている
場合の処理がうまく動作しません。
例えば、
select dblink_exec('conn1', 'insert into emp(name) values (\'AAAAAA\')');
だと正常に動作しますが、AAAAAA に「'」が
含まれている場合、どのようにエスケープすれば
よいのかがわかりません。
色々と試したのですが解決方法が見つからず
こちらに書き込ませていただきました。
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$)');
これでいかがでしょうか？
あるテーブルに更新があった場合に、別DBにある
テーブルを更新したくて
トリガーを作ったのですがうまくいきません。
トリガーから呼んだファンクションで
select dblink_connect（＃＃,＃＃＃）
をどう実行しても。。
ERROR: query has no destination for result data
になってしまいます。
助けてください。
試しに以下のようにやってみたところ、問題なく別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');
-----
ご参考まで。
これってかなり Oracle みたいな感じですね。
でも sys か system とかで悩まないだけいいです。