Full Text Search Engine on Karamelo

Well, will be use PostgreSQL 8.3 FTSE capabilities

The lost of table to search information:

Lessons (title and body fields) News Entries Podcasts Faqs Glossary

CREATE DICTIONARY

CREATE TEXT SEARCH DICTIONARY karamelo_es (

template = snowball,
language = spanish,
stopwords = spanish

);

CREATE TEXT SEARCH CONFIGURATION public.karamelo_es ( COPY = pg_catalog.spanish );

Search:

SELECT news.title FROM news WHERE to_tsvector('karamelo_es', body) @@ to_tsquery('karamelo_es','Educación') ORDER BY created DESC LIMIT 20;

Build index:

CREATE INDEX pgnews_idx ON news USING gin(to_tsvector('karamelo_es', body));

CREATE INDEX pgentr_idx ON entries USING gin(to_tsvector('karamelo_es', body));
CREATE INDEX pgless_idx ON lessons USING gin(to_tsvector('karamelo_es', body));
CREATE INDEX pgglo_idx ON glossaries USING gin(to_tsvector('karamelo_es', definition));
CREATE INDEX pgpod_idx ON podcasts USING gin(to_tsvector('karamelo_es', description));

Search with rank:

SELECT id, title, ts_rank_cd(to_tsvector('karamelo_es', body), to_tsquery('karamelo_es','Educación | sexualidad')) AS rank FROM news, to_tsquery('karamelo_es','Educación | sexualidad') query WHERE to_tsquery('karamelo_es','Educación | sexualidad') @@ to_tsvector('karamelo_es', body) ORDER BY rank DESC LIMIT 20;

Search with rank and ts_headline:

SELECT id, title, ts_headline('karamelo_es', body, to_tsquery('karamelo_es','Educación | sexualidad')), rank FROM ( SELECT id, title, substr(body,0,160) as body, ts_rank_cd(to_tsvector('karamelo_es', body), to_tsquery('karamelo_es','Educación | sexualidad')) AS rank FROM news, to_tsquery('karamelo_es','Educación | sexualidad') query WHERE to_tsquery('karamelo_es','Educación | sexualidad') @@ to_tsvector('karamelo_es', body) ORDER BY rank DESC LIMIT 20 ) AS foo;

substr(