PostgreSQL: Documentation: devel: Additional Features

PostgreSQL: Documentation: devel: Additional Features

PostgreSQL: Documentation: devel: Additional Features

These trigger functions automatically compute a tsvector column from one or more textual columns,
under the control of parameters specified in the CREATE TRIGGER command. An example of their use
is:

CREATE TABLE messages (
    title       text,
    body        text,
    tsv         tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');

SELECT * FROM messages;
   title    |         body          |            tsv
------------+-----------------------+----------------------------
 title here | the body text is here | 'bodi':4 'text':5 'titl':1

SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
   title    |         body
------------+-----------------------
 title here | the body text is here

Having created this trigger, any change in title or body
will automatically be reflected into tsv, without the application having to worry
about it.

posted on 2012-06-09 17:30  lexus 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/lexus/archive/2012/06/09/2543337.html