如何使用pg_trgm的2个表排序Postgres全文检索功能的结果

Question

我有两个表:

Table author
id (pk)  | first_name
1        | John             

Table post
id (pk)  | author_id (fk) | content
1        | 1              | test content

我创建了一个额外的表来保存来自函数的搜索结果:

Table post_search
author_id (fk) | post_id (fk) | created_at | content | first_name

我希望能够提供一个搜索词从前端和全文检索两个表,特别是first_name和content。

我已经在Postgresql上启用了pg_trgm:

CREATE EXTENSION pg_trgm;

这是功能:

CREATE
OR REPLACE FUNCTION public.search_posts(search text)
RETURNS SETOF post_search 
LANGUAGE sql STABLE AS $ function $
SELECT
P.author_id,
P.id,
P.created_at,
P.content,
A.first_name
FROM
post P
JOIN author A ON A.id = P.author_id
WHERE
search <% concat_ws(' ', first_name, content)
ORDER BY
similarity(search, concat_ws(' ', first_name, content))
LIMIT
100;
$function$

该函数的结果不是按first_name排序的，而是混合的，例如。第一个结果在内容上而不是在作者的first_name上搜索关键字。是否有办法按first_name然后按content排序结果?

此外，是否有可能有这样一个函数，而不需要创建额外的post_search表?如果有的话，有人能帮忙写函数代码吗?

更新:

我最终使用这个函数，我已经添加了一个score列和order by它。

CREATE OR REPLACE FUNCTION search_posts(search text)
RETURNS SETOF post_search
LANGUAGE sql
STABLE
AS $function$
SELECT  P.author_id,P.id,P.created_at, P.content, A.first_name, ((similarity(search, P.content) + similarity(search, A.first_name)) / 2) as score
FROM post P
JOIN profile A ON A.id = P.author_id
WHERE search % first_name or search % content
ORDER BY score desc
LIMIT 100;
$function$

Answer 1

另外，是否可能有这样一个函数而不需要创建额外的postrongearch表?

你的问题有一部分很明确，很容易回答。可以声明函数返回一个具有特定结构的匿名表:

....
RETURNS table  (author_id int, post_id int, created_at timestamptz, content text, first_name text)

或者你可以声明一个命名的复合类型

create type post_search as (author_id int, post_id int, created_at timestamptz, content text, first_name text); 

然后:

....
RETURNS setof post_search