postgresql - 使用别名简化PostgreSQL全文搜索tsvector和tsquery

我正试图简化这个查询，因为它将由PHP动态生成，我希望减少处理开销（实际查询将更长，但结构将相同！）.

SELECT title, type_name, ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],
setweight(to_tsvector(coalesce(title,'')), 'A')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B')
,
to_tsquery('search & query'))
FROM TestView WHERE
setweight(to_tsvector(coalesce(title,'')), 'D')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B')
@@
to_tsquery('search & query');

我希望通过定义类似别名的内容来减少指定tsquery和tsvector两次的需要，这样就不必指定两次。类似这样的事情（失败了，我不确定它是否接近正确！）

SELECT title, type_name, ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],
searchvector
,
searchquery
FROM TestView WHERE
setweight(to_tsvector(coalesce(title,'')), 'D')
||
setweight(to_tsvector(coalesce(type_name,'')), 'B') AS searchvector
@@
to_tsquery('search & query') AS searchquery;

这是可能的，还是我只是坚持把它全部生成两次。
For context“TestView”是从多个表生成的视图。
非常感谢您的帮助！

最佳答案

SELECT title,
       type_name,
       ts_rank_cd(ARRAY[0.1,0.2,0.4,1.0],weight,query)
FROM (
    SELECT title,
           type_name,
           setweight(to_tsvector(coalesce(title,'')), 'A')
              ||setweight(to_tsvector(coalesce(type_name,'')), 'B') as weight,
           to_tsquery('search & query') as query
    FROM TestView
) t
WHERE weight @@ query