CREATE EXTENSION pg_trgm;

CREATE OR REPLACE FUNCTION combined_search(search_term text)
RETURNS TABLE(id integer, title text, rank float) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.id,
        t.title,
        -- 结合全文搜索和 trigram 相似度的加权排名
        (ts_rank(to_tsvector('english', t.title), plainto_tsquery('english', search_term)) * 0.7 +
         similarity(t.title, search_term) * 0.3) AS rank
    FROM 
        articles t
    WHERE 
        to_tsvector('english', t.title) @@ plainto_tsquery('english', search_term)
        OR t.title % search_term
    ORDER BY 
        rank DESC;
END;
$$ LANGUAGE plpgsql;

-- 全文搜索索引
CREATE INDEX idx_fts_title ON articles USING gin(to_tsvector('english', title));

-- Trigram 索引
CREATE INDEX idx_trgm_title ON articles USING gin(title gin_trgm_ops);

-- 简单查询
SELECT * FROM combined_search('database management');

-- 带阈值的查询
SELECT * FROM combined_search('database management') WHERE rank > 0.1;