Translate problems
От | Leandro Fanzone |
---|---|
Тема | Translate problems |
Дата | |
Msg-id | 3D2C4CC8.4080808@hasar.com обсуждение исходный текст |
Ответы |
Re: Translate problems
Re: Translate problems |
Список | pgsql-novice |
I have a table with a text field: CREATE TABLE mytable(myfield text); I want to perform over it a search of a kind I call "foreign characters insensitive". My native language is Spanish, and we have accented characters (acute accent over vowels), for example. As not everyone who will use the application is completely aware of the accentuation rules, data can be entered with inaccuracies regarding the accents, and also those who search data can fail to reproduce the accentuation of the target text being searched. So the solution would be to transform the data to a neutral field where each vowel that is found with accent would be transformed to its corresponding vowel without the accent, both in the selected field and in the text to be compared to. So far, so good. There is a builtin function called "translate", and this selection works OK: SELECT myfield FROM mytable WHERE TRANSLATE(LOWER(myfield), '[accented vowels collection]', '[respetive non-accented vowels]') LIKE TRANSLATE(LOWER('something%'), '[accented vowels collection]', '[respetive non-accented vowels]'); where "[accented vowels collection]" and "[respetive non-accented vowels]" are the real characters, naturally. Now, as I want to optimize this search, I would like to create an index to it. Before there was this: CREATE INDEX myfield_index ON mytable(LOWER(myfield)); to perform indexed searches on queries like SELECT myfield FROM mytable WHERE myfield LIKE 'something%'; But when I tried to create an index using TRANSLATE: CREATE INDEX myfield_index ON mytable(TRANSLATE(LOWER(myfield), '[accented vowels collection]', '[respetive non-accented vowels]')); I had this error: ERROR: parser: parse error at or near "(" So I created a function to do the work: CREATE FUNCTION plain_text(text) RETURNS text AS ' BEGIN RETURN TRANSLATE(LOWER($1), ''[accented vowels collection]'', ''[respetive non-accented vowels]''); END; ' LANGUAGE 'plpgsql' WITH(iscachable); and built an index with it, and everything worked perfect. I wonder why plain TRANSLATE doesn't work within the index creation, just curiousity. I suppose using directly "translate" should be faster than calling plpgsql. By the way, the creation of an index using a function is a great feature for things like this! Leandro Fanzone.
В списке pgsql-novice по дате отправления: