如何在PostgreSQL中创建n-gram

Question

如何在PostgreSQL中创建n-gram

postgresqln-gramtrigram

3

我希望我的应用程序具有搜索功能。

为了实现这一点，我使用了三元组，并且它运行良好。

问题是：

三元组是创建单词的三个字符组的序列。我希望单个对象中有超过3个字符。例如：

select show_trgm('abcpqrs');

这将返回：{" a"," ab","abc","bcp","cpq","pqr","qrs","rs "} 我想要：{" a"," ab","abc","bcp","cpq","pqr","qrs","rs ","abcd","bcpq","cpqr"...} 如何实现这个目标？

- Soham Navadiya

你可以创建一个用户定义的函数来完成这个任务。 - Tim Biegeleisen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cachique · Accepted Answer

试试这个函数。

CREATE OR REPLACE FUNCTION myngram(mystr TEXT, n INT) RETURNS TEXT[]
AS $$
DECLARE
str VARCHAR;
arr TEXT[];
BEGIN

  str := lpad(mystr, n - 1 + char_length(mystr), ' ');
  str := rpad(str, n - 1 + char_length(str), ' ');

  arr := array[]::TEXT[];
  FOR i IN 1 .. char_length(str) - n + 1 LOOP
    arr := arr || substring(str from i for n);
  END LOOP;
  RETURN arr;
END
$$
LANGUAGE plpgsql;

我的测试结果。

testdb=# SELECT myngram('abcpqrs', 4);
                             myngram                             
-----------------------------------------------------------------
 {"   a","  ab"," abc",abcp,bcpq,cpqr,pqrs,"qrs ","rs  ","s   "}
(1 row)

testdb=# SELECT myngram('abcpqrs', 5);
                                       myngram                                       
-------------------------------------------------------------------------------------
 {"    a","   ab","  abc"," abcp",abcpq,bcpqr,cpqrs,"pqrs ","qrs  ","rs   ","s    "}
(1 row)