我在找我的搜索功能的回顾。详情如下。
我有以下表格：

CREATE TABLE table_a (
    id serial PRIMARY KEY,
    text_a text UNIQUE
    -- other fields ...
);

CREATE TABLE table_b (
    id serial PRIMARY KEY,
    table_a_id integer NOT NULL REFERENCES table_a(id), 
    text_b text,
    -- other fields ...
    unique (table_a_id, text_b)
);

我要去查一下 table_a 文本组合自 text_a 以及 text_b 并返回一个包含 table_a 示例，但不重复，例如，搜索可能从 table_b 这说明了同样的问题 text_a ，在这种情况下，响应应该包含来自 table_a 每次只有一次 table_a 示例。
另一种规格是把更多的重量放在 text_a .
我已经有这个工作，但也许它可以做得更好，这里是代码：

CREATE EXTENSION pg_trgm;

CREATE INDEX table_a_text_a_gin_idx ON table_a
USING GIN ((text_a) gin_trgm_ops);

CREATE INDEX table_b_text_a_gin_idx ON table_b
USING GIN ((text_b) gin_trgm_ops);

CREATE FUNCTION search_combined_text(search text)
RETURNS SETOF table_a AS $$
    WITH summary AS (
    SELECT
        a.*,
        GREATEST(
            SIMILARITY(concat_ws(' ', a.text_a, b.text_b), search),
            SIMILARITY(a.text_a, search) * 1.5
            ) as sml,
        ROW_NUMBER() OVER(PARTITION BY a.id) AS rk
    FROM table_a AS a
    INNER JOIN table_b AS b ON a.id = b.table_a_id
    WHERE (
        SIMILARITY(concat_ws(' ', a.text_a, b.text_b), search) > 0.3 OR
        SIMILARITY(a.text_a, search) * 1.5 > 0.3
        )
    ORDER BY sml DESC
    )
    SELECT s.id, s.text_a -- other fields..., except s.sml and s.rk
    FROM summary s WHERE s.rk = 1;
$$ LANGUAGE sql STABLE;