PostgreSQL使用pg_trgm双表全文搜索排序及无表函数实现

安全阿岳

2026-10-1

排序优化方案

你当前用两个字段相似度取平均的逻辑权重是1:1，自然不会优先匹配first_name的结果，要实现需求可以用以下两种方式：

方案1：直接按字段优先级排序

完全满足「先按first_name匹配度排序、再按content匹配度排序」的硬优先级要求，排序逻辑直接写在ORDER BY子句中：

ORDER BY similarity(search, A.first_name) DESC, similarity(search, P.content) DESC

方案2：加权得分排序

给first_name分配更高的权重系数，权重可以灵活调整，比如设置first_name权重是content的2倍，计算逻辑如下：

-- 得分公式：(first_name相似度 * 2 + content相似度) / 3，保证得分区间仍为0~1
(similarity(search, A.first_name) * 2 + similarity(search, P.content)) / 3 AS score

如果需要更高的优先级可以继续调高first_name的权重系数。

无需预创建post_search表的实现

PostgreSQL支持RETURNS TABLE语法，可以直接在函数定义中声明返回的结构，不需要提前创建实体表存储结果，完整函数代码如下：

CREATE OR REPLACE FUNCTION public.search_posts(search text)
    RETURNS TABLE (
        author_id int,
        post_id int,
        created_at timestamptz,
        content text,
        first_name text,
        score numeric
    )
    LANGUAGE sql STABLE
AS $function$
SELECT
    P.author_id,
    P.id AS post_id,
    P.created_at,
    P.content,
    A.first_name,
    -- 这里用加权得分，你也可以换成方案1的排序逻辑
    (similarity(search, A.first_name) * 2 + similarity(search, P.content)) / 3 AS score
FROM post P
JOIN author A ON A.id = P.author_id
WHERE search % A.first_name OR search % P.content
ORDER BY score DESC
LIMIT 100;
$function$