Re: Full Text Index Scanning

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 <oleg(at)sai(dot)msu(dot)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 <oleg(at)sai(dot)msu(dot)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 <matt(at)warnertechnology(dot)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 <oleg(at)sai(dot)msu(dot)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: oleg(at)sai(dot)msu(dot)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: oleg(at)sai(dot)msu(dot)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: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>