postgresql - 在 phraseto_tsquery 中添加多个短语

Question

我已经成功地将单个单词的数组连接到 to_tsquery 的字符串中，但是 postgres 9.6 中的 phraseto_tsquery 只允许一个关键字短语。有谁知道以这种方式查询 tsvector(无论是在 Sql 还是全文搜索功能中)的解决方案，我可以将动态数量的短语(或/与)查询到查询中。选择 block 都是文本数组。

第一次尝试:

SELECT to_tsvector('english','Try not to become a man of successful companies, but rather try to become a man of value')
   @@ (to_tsquery('english','man & become')
       && phraseto_tsquery('english','man of value')
       && phraseto_tsquery('english','company')
       || phraseto_tsquery('english', 'company | man of value')
   );

现实世界中寻找动物的问题示例:

-- with statements here of opp_tsv and tp
SELECT
  tp.id,
  tp.keywords, --['giraffes','lions', 'monkeys']
  tp.phrase_keywords, --['pygmy marmocet','African Lion']
  tp.neg_keywords, --['aliens', 'spaceships', 'space']
  tp.neg_phrase_keywords --['Andromedan Alien', 'Nibiru Reptilian']
FROM tp, opp_tsv,
  -- string logic for ts_query
      concat(array_to_string(tp.keywords, ' | ')) AS kws_concat,
      concat(array_to_string(tp.neg_keywords, ' | ')) AS     neg_kws_concat,
      to_tsquery('english', kws_concat) query,
      to_tsquery('english', concat(neg_kws_concat)) neg_query
  -- Case logic for phrase queries

  -- .... -> phrase_query,
      phraseto_tsquery('phrase to search | Need this phrase too')
  -- .... -> phrase_neg_query,

WHERE
  (
    opp_tsv.doc @@ query --pos
    OR
    opp_tsv.doc @@ phrase_query --pos
  )
  AND NOT (
    opp_tsv.doc @@ neg_query --neg
    OR
    opp_tsv.doc @@ phrase_neg_query --neg
  )
ORDER BY rank_cd DESC;

想法:根据数组长度动态生成

opp_tsv.doc @@ (phrase_query || phrase_query2)

或以某种方式实现这一目标

opp_tsv.doc @@ phraseto_tsquery('big messy phrase | more messy wordphrases')

编辑: SELECT phraseto_tsquery('phrase to search | Need this phrase too')结果 = 'phrase' <-> 'to' <-> 'search' <-> 'need' <-> 'this' <-> 'phrase' <-> 'too'我正在寻找的是 'phrase<->to<->search' | 'need<->this<->phrase<->too' 的结果

Answer 1

You can define your own aggregate通过 tsquery 或 (||) 运算符:

CREATE AGGREGATE tsquery_or_agg(tsquery) (
  SFUNC = tsquery_or,
  STYPE = tsquery
);

注意:上面的聚合依赖于 tsquery 的 || 运算符由 tsquery_or(tsquery , tsquery) 函数。您可以通过以下方式检查:

SELECT *
FROM   pg_operator
WHERE  oprname  = '||'
AND    oprleft  = regtype 'tsquery'
AND    oprright = regtype 'tsquery';

如果您不想依赖这个(未记录的)函数的名称(即使它不太可能更改)，您可以创建自己的函数作为基本函数 (SFUNC)对于您的总计:

CREATE FUNCTION my_tsquery_or(tsquery, tsquery)
  RETURNS tsquery
  LANGUAGE sql
  IMMUTABLE
  STRICT
  AS 'SELECT $1 || $2';

之后，您的查询将类似于:

WITH tp(id, keywords, phrase_keywords, neg_keywords, neg_phrase_keywords ) AS (
  VALUES (42, ARRAY['giraffes', 'lions', 'monkeys']::text[],
              ARRAY['pygmy marmocet', 'African Lion']::text[],
              ARRAY['aliens', 'spaceships', 'space']::text[],
              ARRAY['Andromedan Alien', 'Nibiru Reptilian']::text[])
),
tq(id, query) AS (
  SELECT   tp.id,
           (((SELECT tsquery_or_agg(plainto_tsquery(kw)) FROM unnest(keywords) kw) ||
             (SELECT tsquery_or_agg(phraseto_tsquery(pk)) FROM unnest(phrase_keywords) pk)) &&
             !!((SELECT tsquery_or_agg(plainto_tsquery(nk)) FROM unnest(neg_keywords) nk) ||
                (SELECT tsquery_or_agg(phraseto_tsquery(np)) FROM unnest(neg_phrase_keywords) np)))
  FROM     tp
),
opp_tsv(doc) AS (
  VALUES (to_tsvector('Earth''s African Lions')),
         (to_tsvector('Andromedan Alien''s space monkeys'))
)
SELECT   tp.id,
         tp.keywords,
         tp.phrase_keywords,
         tp.neg_keywords,
         tp.neg_phrase_keywords,
         opp_tsv.doc
FROM     opp_tsv, tp
JOIN     tq USING (id)
WHERE    opp_tsv.doc @@ tq.query
ORDER BY ts_rank_cd(opp_tsv.doc, tq.query) DESC;

此外，tp 中的if 字段可以包含像'big messy phrase | 这样的短语。更多困惑的词组'，那么你一开始就没有正确地分割你的输入。您可以使用 regexp_split_to_table() 函数拆分此类短语/关键字。这样，tq CTE 应该类似于:

tq(id, query) AS (
  SELECT   tp.id,
           (((SELECT tsquery_or_agg(plainto_tsquery(kw)) FROM unnest(keywords) kwb, regexp_split_to_table(kwb, '\|') kw) ||
             (SELECT tsquery_or_agg(phraseto_tsquery(pk)) FROM unnest(phrase_keywords) pkb, regexp_split_to_table(pkb, '\|') pk)) &&
             !!((SELECT tsquery_or_agg(plainto_tsquery(nk)) FROM unnest(neg_keywords) nkb, regexp_split_to_table(nkb, '\|') nk) ||
                (SELECT tsquery_or_agg(phraseto_tsquery(np)) FROM unnest(neg_phrase_keywords) npb, regexp_split_to_table(npb, '\|') np)))
  FROM     tp
),