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
>>
>
>
