Re: Updated tsearch documentation

On Tue, 17 Jul 2007, Oleg Bartunov wrote:

> On Tue, 17 Jul 2007, Bruce Momjian wrote:
>
>> I think the tsearch documentation is nearing completion:
>>
>> http://momjian.us/expire/fulltext/HTML/textsearch.html
>>
>> but I am not happy with how tsearch is enabled in a user table:
>>
>> http://momjian.us/expire/fulltext/HTML/textsearch-app-tutorial.html
>>
>> Aside from the fact that it needs more examples, it only illustrates an
>> example where someone creates a table, populates it, then adds a
>> tsvector column, populates that, then creates an index.
>>
>> That seems quite inflexible. Is there a way to avoid having a separate
>> tsvector column? What happens if the table is dynamic? How is that
>> column updated based on table changes? Triggers? Where are the
>> examples? Can you create an index like this:
>
> I agree, that there are could be more examples, but text search doesn't
> require something special !
> *Example* of trigger function is documented on
> http://momjian.us/expire/fulltext/HTML/textsearch-opfunc.html
>

Bruce,

below is an example of trigger for insert/update of example table

create function pgweb_update() returns trigger as
$$
BEGIN
NEW.textsearch_index=
setweight( to_tsvector( coalesce (title,'')), 'A' ) || ' ' ||
setweight( to_tsvector(coalesce (body,'')),'D'); RETURN NEW;
END;
$$
language plpgsql;

CREATE TRIGGER fts_update BEFORE INSERT OR UPDATE ON pgweb
FOR EACH ROW EXECUTE PROCEDURE pgweb_update();

>
>>
>> CREATE INDEX textsearch_id ON pgweb USING gin(to_tsvector(column));
>>
>> That avoids having to have a separate column because you can just say:
>>
>> WHERE to_query('XXX') @@ to_tsvector(column)
>
> yes, it's possible, but without ranking, since currently it's impossible to
> store any information in index (it's pg's feature). btw, this should
> works and for GiST index also.
>
> That kind of search is useful if there is another natural ordering of search
> results, for example, by timestamp.
>
>>
>> How do we make sure that the to_query is using the same text search
>> configuration as the 'column' or index? Perhaps we should suggest:
>
> please, keep in mind, it's not mandatory to use the same configuration
> at search time, that was used at index creation.
>

one example is when text search index created without taking into account
stop-words. Then you could search famous 'to be or not to be' with the
same configuration, or ignore stop words with other.

>>
>> CREATE INDEX textsearch_idx ON pgweb USING
>> gin(to_tsvector('english',column));
>>
>> so that at least the configuration is documented in the index.
>
> yes, it's better to always explicitly specify configuration name and not rely
> on default configuration. Unfortunately, configuration name doesn't saved in
> the index.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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