Doesn't seem to work either. Maybe something changed in 9.1? create index test_idx on testtable using gin(to_tsvector(wordcolumn||' '||reverse(wordcolumn))); ERROR: functions in index expression must be marked IMMUTABLE
On Sun, Jan 30, 2011 at 3:28 AM, Oleg Bartunov <o...@sai.msu.su> wrote: > I used 9.1dev, but you can try immutable function (from > http://andreas.scherbaum.la/blog/archives/10-Reverse-a-text-in-PostgreSQL.html > ) > > create function reverse(text) returns text as $$ > > select case when length($1)>0 > > then substring($1, length($1), 1) || reverse(substring($1, 1, > length($1)-1)) > > else '' end $$ language sql immutable strict; > > > > On Sat, 29 Jan 2011, Matt Warner wrote: > > 9.0.2 >> >> On Sat, Jan 29, 2011 at 9:35 AM, Oleg Bartunov <o...@sai.msu.su> wrote: >> >> What version of Pg you run ? Try latest version. >>> >>> Oleg >>> >>> >>> On Sat, 29 Jan 2011, Matt Warner wrote: >>> >>> Reverse isn't a built-in Postgres function, so I found one and installed >>> >>>> it. >>>> However, attempting to use it in creating an index gets me the message >>>> "ERROR: functions in index expression must be marked IMMUTABLE", even >>>> though the function declaration already has the immutable argument. >>>> >>>> Is there a specific version of the reverse function you're using? Or am >>>> I >>>> just missing something obvious? This is Postgres 9, BTW. >>>> >>>> Thanks, >>>> >>>> Matt >>>> >>>> On Sat, Jan 29, 2011 at 6:46 AM, Matt Warner <m...@warnertechnology.com >>>> >>>>> wrote: >>>>> >>>> >>>> Thanks Oleg. I'm going to have to experiment with this so that I >>>> >>>>> understand >>>>> it better. >>>>> >>>>> Matt >>>>> >>>>> >>>>> On Fri, Jan 28, 2011 at 1:12 PM, Oleg Bartunov <o...@sai.msu.su> >>>>> wrote: >>>>> >>>>> Matt, I'd try to use prefix search on original string concatenated >>>>> with >>>>> >>>>>> reverse string: >>>>>> >>>>>> Just tried on some spare table >>>>>> >>>>>> knn=# \d spot_toulouse >>>>>> Table "public.spot_toulouse" >>>>>> Column | Type | Modifiers >>>>>> ---------------------+-------------------+----------- >>>>>> clean_name | character varying | >>>>>> >>>>>> >>>>>> 1. create index knn=# create index clean_name_tlz_idx on spot_toulouse >>>>>> using gin(to_tsvector('french', clean_name || ' ' || >>>>>> reverse(clean_name))); >>>>>> 2. >>>>>> select clean_name from spot_toulouse where to_tsvector('french', >>>>>> clean_name|| ' ' || reverse(clean_name) ) @@ >>>>>> to_tsquery('french','the:* >>>>>> | >>>>>> et:*'); >>>>>> >>>>>> Select looks cumbersome, but you can always write wrapper functions. >>>>>> The >>>>>> only drawback I see for now is that ranking function will a bit >>>>>> confused, >>>>>> since coordinates of original and reversed words will be not the same, >>>>>> but >>>>>> again, it's possible to obtain tsvector by custom function, which >>>>>> aware >>>>>> about reversing. >>>>>> >>>>>> Good luck and let me know if this help you. >>>>>> >>>>>> Oleg >>>>>> >>>>>> >>>>>> On Fri, 28 Jan 2011, Matt Warner wrote: >>>>>> >>>>>> I'm in the process of migrating a project from Oracle to Postgres and >>>>>> >>>>>> have >>>>>>> run into a feature question. I know that Postgres has a full-text >>>>>>> search >>>>>>> feature, but it does not allow scanning the index (as opposed to the >>>>>>> data). >>>>>>> Specifically, in Oracle you can do "select * from table where >>>>>>> contains(colname,'%part_of_word%')>1". While this isn't terribly >>>>>>> efficient, >>>>>>> it's much faster than full-scanning the raw data and is relatively >>>>>>> quick. >>>>>>> >>>>>>> It doesn't seem that Postgres works this way. Attempting to do this >>>>>>> returns >>>>>>> no rows: "select * from table where to_tsvector(colname) @@ >>>>>>> to_tsquery('%part_of_word%')" >>>>>>> >>>>>>> The reason I want to do this is that the partial word search does not >>>>>>> involve dictionary words (it's scanning names). >>>>>>> >>>>>>> Is this something Postgres can do? Or is there a different way to do >>>>>>> scan >>>>>>> the index? >>>>>>> >>>>>>> TIA, >>>>>>> >>>>>>> Matt >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> >>>>>> Oleg >>>>>> _____________________________________________________________ >>>>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru >>>>>> ), >>>>>> Sternberg Astronomical Institute, Moscow University, Russia >>>>>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ >>>>>> phone: +007(495)939-16-83, +007(495)939-23-83 >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> Regards, >>> Oleg >>> _____________________________________________________________ >>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), >>> Sternberg Astronomical Institute, Moscow University, Russia >>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ >>> phone: +007(495)939-16-83, +007(495)939-23-83 >>> >>> >> > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 >
