1 -- use the tsearch2 fulltextsearch extension: (recommended)
3 -- $ psql phpwiki < /usr/share/postgresql/contrib/tsearch2.sql
7 \set httpd_user 'phpwiki'
9 -- example of ISpell dictionary:
10 -- UPDATE pg_ts_dict SET dict_initoption='DictFile="/usr/local/share/ispell/russian.dict" ,AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"' WHERE dict_name='ispell_template';
11 -- example of synonym dict:
12 -- UPDATE pg_ts_dict SET dict_initoption='/usr/local/share/ispell/english.syn' WHERE dict_id=5;
14 -- this might be needed:
15 -- see http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
16 -- update pg_ts_cfg set locale='en_US.UTF-8' where ts_name='default';
18 -- ================================================================
20 -- Don't modify below this point unless you know what you are doing.
22 -- ================================================================
24 \set qprefix '\'' :prefix '\''
25 \set qhttp_user '\'' :httpd_user '\''
26 \echo ' prefix = ' :qprefix
27 \echo ' httpd_user = ' :qhttp_user
29 \set version_tbl :prefix 'version'
31 GRANT SELECT ON pg_ts_dict, pg_ts_parser, pg_ts_cfg, pg_ts_cfgmap TO :httpd_user;
32 ALTER TABLE :version_tbl ADD COLUMN idxFTI tsvector;
33 UPDATE :version_tbl SET idxFTI=to_tsvector('default', content);
35 CREATE INDEX idxFTI_idx ON :version_tbl USING gist(idxFTI);
37 CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON :version_tbl
38 FOR EACH ROW EXECUTE PROCEDURE tsearch2(idxFTI, content);