如何获取PostgreSQL全文搜索（FTS）匹配结果的起止位置？

问题背景 #

在使用PostgreSQL的全文搜索（FTS）做匹配检测时，希望获取匹配内容在原文本中的起始和结束位置。例如：

检测匹配的SQL：

select to_tsvector('simple', 'Free text seaRCh is a wONderful Thing') @@ phraseto_tsquery('simple', 'wonderful thing');

使用ts_headline函数可高亮匹配内容：

select ts_headline('Free text seaRCh is a wonderful Thing',
        phraseto_tsquery('simple', 'wonderful thing'));

执行结果：

ts_headline
═════════════════════════════════════════════════════
 Free text seaRCh is a <b>wonderful</b> <b>Thing</b>
(1 row)

既然ts_headline能识别匹配位置，如何直接提取这些起止位置信息？

解决方案 #

PostgreSQL没有直接返回匹配起止位置的内置函数，但可通过以下几种方式实现：

1. 解析ts_headline的输出结果 #

利用ts_headline给匹配内容添加的标签（默认<b>/</b>），通过字符串处理函数提取位置：

WITH headline_result AS (
    SELECT ts_headline('Free text seaRCh is a wonderful Thing', phraseto_tsquery('simple', 'wonderful thing')) AS hl
)
SELECT 
    -- 第一个匹配项的起始位置（跳过<b>标签长度）
    strpos(hl, '<b>') + 3 AS start_pos,
    -- 第一个匹配项的结束位置（减去</b>标签长度）
    strpos(hl, '</b>') - 1 AS end_pos,
    -- 提取匹配内容
    substring(hl FROM strpos(hl, '<b>') + 3 FOR strpos(hl, '</b>') - strpos(hl, '<b>') - 3) AS matched_text
FROM headline_result;

说明：若存在多个匹配项，需用正则表达式拆分或循环处理所有标签对。

2. 结合ts_debug与字符串定位 #

通过ts_debug分解文本的词素及起始位置，再匹配查询词：

WITH text_data AS (
    SELECT 'Free text seaRCh is a wonderful Thing' AS original_text,
           phraseto_tsquery('simple', 'wonderful thing') AS query
),
tokenized AS (
    SELECT ts_debug('simple', original_text) AS debug_info FROM text_data
),
parsed_tokens AS (
    SELECT 
        (debug_info).token AS token,
        (debug_info).positions AS positions
    FROM tokenized
)
SELECT 
    original_text,
    unnest(positions) AS start_pos,
    unnest(positions) + length(token) - 1 AS end_pos,
    token
FROM text_data, parsed_tokens
WHERE to_tsvector('simple', token) @@ query;

说明：ts_debug返回的positions字段是词素在原文本中的起始索引，结合词素长度可计算结束位置。

3. 封装自定义函数 #

若需频繁提取位置，可编写PL/pgSQL函数封装上述逻辑，直接返回所有匹配项的起止位置集合，简化调用流程。

内容的提问来源于stack exchange，提问作者chhenning