PostgreSQLでRastを使った全文検索ができるモジュールが登場したので早速試してみる。（pgsql-jp: 35758）
まずはMakefileを修正。
aprとdb4をDarwinPortsでインストールし、Rastは自前で/usr/localにインストールしたので以下のようにした。（OSはMac OS X 10.4.2）
diff -u Makefile.orig Makefile --- Makefile.orig 2005-07-28 23:49:43.000000000 +0900 +++ Makefile 2005-07-28 23:53:09.000000000 +0900 @@ -13,15 +13,15 @@ DOCS = README.pg_rast DATA_built = pg_rast.sql -PG_CPPFLAGS += -Wno-unused -I/usr/local/apr/include/apr-1 -SHLIB_LINK += -L/usr/local/apr/lib -lrast -lapr-1 +PG_CPPFLAGS += -Wno-unused -I/usr/local/include -I/opt/local/include/apr-0 +SHLIB_LINK += -L/usr/local/lib -L/opt/local/lib -lrast -lapr-0 ifdef USE_PGXS PGXS = $(shell pg_config --pgxs) include $(PGXS) else subdir = contrib/pg_rast -top_builddir = /home/snaga/pgsql/source/postgresql-8.0.3 +top_builddir = /Users/ryusuke/.build/postgresql-8.0.3 include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif
で、makeしてインストール＆関数を登録。
cd postgresql-8.0.3 ./configure --prefix=/usr/local/pgsql cd contrib/pg_rast make sudo make install sed 's@\(\$libdir/pg_rast\)@\1.so@' pg_rast.sql > pg_rast.so.sql sudo -u pgsql psql -f pg_rast.so.sql template1 sudo -u pgsql psql -f pg_rast.so.sql test
インストールが終わったら、実際に使ってみる。
CREATE TABLE t1 (id INTEGER, doc TEXT);
SELECT pg_rast_createdb('/usr/local/pgsql/rast/t1', 't1', 'id', 'doc');
INSERT INTO t1 VALUES (1, 'The quick brown fox jumps over the lazy dog.');
INSERT INTO t1 VALUES (2, 'The quick brown dog jumps over the lazy fox.');
INSERT INTO t1 VALUES (3, 'The quick brown fox jumps over the lazy cat.');
INSERT INTO t1 VALUES (4, 'The quick brown cat jumps over the lazy fox.');
INSERT INTO t1 VALUES (5, 'The quick brown dog jumps over the lazy cat.');
INSERT INTO t1 VALUES (6, 'The quick brown cat jumps over the lazy dog.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '1', 'The quick brown fox jumps over the lazy dog.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '2', 'The quick brown dog jumps over the lazy fox.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '3', 'The quick brown fox jumps over the lazy cat.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '4', 'The quick brown cat jumps over the lazy fox.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '5', 'The quick brown dog jumps over the lazy cat.');
SELECT pg_rast_register('/usr/local/pgsql/rast/t1', 't1', '6', 'The quick brown cat jumps over the lazy dog.');
SELECT * FROM t1 WHERE id IN (SELECT pg_rast_search('/usr/local/pgsql/rast/t1', 'dog')::INTEGER);
NOTICE: hit_count: 4
id | doc
----+----------------------------------------------
1 | The quick brown fox jumps over the lazy dog.
2 | The quick brown dog jumps over the lazy fox.
5 | The quick brown dog jumps over the lazy cat.
6 | The quick brown cat jumps over the lazy dog.
(4 rows)
行数が少ないのでこの例だとLIKE演算子を使っても十分なパフォーマンスが得られるのだけど、とりあえずOK.
でも毎回 SELECT pg_rast_register(); は面倒なのでトリガを作る。
CREATE OR REPLACE FUNCTION my_rast_trigger_func() RETURNS TRIGGER AS ' DECLARE sql TEXT; BEGIN sql := ''SELECT pg_rast_register('' || quote_literal(''/usr/local/pgsql/rast/t1'') || '','' || quote_literal(TG_RELNAME) || '','' || quote_literal(NEW.id::TEXT) || '','' || quote_literal(NEW.doc) || '');''; EXECUTE sql; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER my_rast_trigger AFTER INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE my_rast_trigger_func(); INSERT INTO t1 VALUES (7, 'cogito ergo sum.'); INSERT INTO t1 VALUES (8, 'que sera sera.'); SELECT * FROM t1 WHERE id IN (SELECT pg_rast_search('/usr/local/pgsql/rast/t1', 'ergo | sera')::INTEGER); NOTICE: hit_count: 2 id | doc ----+------------------ 7 | cogito ergo sum. 8 | que sera sera. (2 rows)
これでずいぶん簡単に使えるようになった。
現状ではUPDATE/DELETE時のトリガとして使える関数が無いので一度登録した内容の変更・削除が難しいけど、それは今後に期待。
