Re: Full text index without accents
От | Fco. Mario Barcala" Rodríguez |
---|---|
Тема | Re: Full text index without accents |
Дата | |
Msg-id | 1216889206.5112.11.camel@tambre обсуждение исходный текст |
Ответ на | Re: Full text index without accents (Oleg Bartunov <oleg@sai.msu.su>) |
Ответы |
Re: Full text index without accents
|
Список | pgsql-general |
Finally I create a function like: CREATE OR REPLACE FUNCTION nonsensible (text) RETURNS text AS $$ DECLARE var1 varchar; BEGIN var1=replace($1, 'á', 'a'); var1=replace(var1, 'é', 'e'); var1=replace(var1, 'í', 'i'); var1=replace(var1, 'ó', 'o'); var1=replace(var1, 'ú', 'u'); var1=replace(var1, 'Á', 'A'); var1=replace(var1, 'É', 'E'); var1=replace(var1, 'Í', 'I'); var1=replace(var1, 'Ó', 'O'); var1=replace(var1, 'Ú', 'U'); return var1; END $$LANGUAGE plpgsql immutable; Then, create text indexes, one for sensible queries and other for unsensible ones: CREATE INDEX textindex ON document USING gin(to_tsvector('spanish',text)); CREATE INDEX textindexn ON document USING gin(to_tsvector('spanish',nonsensible(text))); And then make a query sensible or unsensible to accents doing: SELECT id FROM document WHERE to_tsvector('spanish',text) @@ to_tsquery('spanish','word_with_accent'); or: SELECT id FROM document WHERE to_tsvector('spanish',nonsensible(text)) @@ to_tsquery('spanish',nonsensible('word_with_accent')); respectively. I think postgreSQL uses both indexes as necessary. I believe to remember reading something about it in the documentation. Thank you very much, Mario Barcala > Here is an example > > CREATE FUNCTION dropatsymbol(text) RETURNS text > AS 'select replace($1, ''@'', '' '');' > LANGUAGE SQL; > > arxiv=# select to_tsvector('english',dropatsymbol('oleg@sai.msu.su')); > to_tsvector > ------------------------- > 'oleg':1 'sai.msu.su':2
В списке pgsql-general по дате отправления: