PostgreSQL: Documentation: devel: Additional Features

最新推荐文章于 2025-09-05 14:24:44 发布

weixin_34194551

最新推荐文章于 2025-09-05 14:24:44 发布

阅读量59

点赞数

文章标签：数据库

本文介绍如何使用 PostgreSQL 的触发器函数自动更新 tsvector 类型的列，该功能简化了全文搜索索引的维护过程。通过创建一个触发器，可以在插入或更新记录时自动同步文本字段到用于全文搜索的 tsvector 列。

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.