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 ([[BR]]
    template = snowball,[[BR]]
    language = spanish,[[BR]]
    stopwords = spanish[[BR]]
);

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));[[BR]]

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

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;