Re: Updated tsearch documentation
От | Oleg Bartunov |
---|---|
Тема | Re: Updated tsearch documentation |
Дата | |
Msg-id | Pine.LNX.4.64.0707171352190.20068@sn.sai.msu.ru обсуждение исходный текст |
Ответ на | Re: Updated tsearch documentation (Oleg Bartunov <oleg@sai.msu.su>) |
Список | pgsql-hackers |
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@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
В списке pgsql-hackers по дате отправления:
Следующее
От: "Zeugswetter Andreas ADI SD"Дата:
Сообщение: Re: Straightforward changes for increased SMP scalability