此模块实现了一个数据类型 ltree
,用于表示存储在层次树状结构中的数据的标签。提供了用于搜索标签树的扩展功能。
此模块被认为是 “受信任的”,也就是说,它可以由对当前数据库具有 CREATE
权限的非超级用户安装。
标签 是字母数字字符、下划线和连字符的序列。有效的字母数字字符范围取决于数据库语言环境。例如,在 C 语言环境中,允许使用字符 A-Za-z0-9_-
。标签长度不得超过 1000 个字符。
示例:42
、Personal_Services
标签路径 是由点分隔的零个或多个标签的序列,例如 L1.L2.L3
,表示从层次树的根到特定节点的路径。标签路径的长度不能超过 65535 个标签。
示例:Top.Countries.Europe.Russia
ltree
模块提供了多种数据类型
ltree
存储标签路径。
lquery
表示用于匹配 ltree
值的类似正则表达式的模式。简单的单词匹配路径中的该标签。星号 (*
) 匹配零个或多个标签。这些可以与点连接以形成必须匹配整个标签路径的模式。例如
foo Match the exact label pathfoo
*.foo.* Match any label path containing the labelfoo
*.foo Match any label path whose last label isfoo
星号和简单的单词都可以进行量化,以限制它们可以匹配的标签数
*{n
} Match exactlyn
labels *{n
,} Match at leastn
labels *{n
,m
} Match at leastn
but not more thanm
labels *{,m
} Match at mostm
labels — same as *{0,m
} foo{n
,m
} Match at leastn
but not more thanm
occurrences offoo
foo{,} Match any number of occurrences offoo
, including zero
在没有明确量词的情况下,星号的默认值为匹配任意数量的标签(即 {,}
),而非星号项的默认值为精确匹配一次(即 {1}
)。
有几个修饰符可以放在非星号 lquery
项的末尾,使其匹配的不仅仅是精确匹配
@ Match case-insensitively, for examplea@
matchesA
* Match any label with this prefix, for examplefoo*
matchesfoobar
% Match initial underscore-separated words
%
的行为有点复杂。它尝试匹配单词,而不是整个标签。例如,foo_bar%
匹配 foo_bar_baz
,但不匹配 foo_barbaz
。如果与 *
结合使用,则前缀匹配分别应用于每个单词,例如 foo_bar%*
匹配 foo1_bar2_baz
,但不匹配 foo1_br2_baz
。
此外,你可以编写几个可能修改的非星号项,用 |
(OR)分隔,以匹配其中任何一项,你还可以将 !
(NOT)放在非星号组的开头,以匹配不匹配任何备选项的任何标签。如果有量词,则放在组的末尾;它表示对整个组的匹配次数(即,匹配或不匹配任何备选项的标签数)。
以下是 lquery
的带注释示例
Top.*{0,2}.sport*@.!football|tennis{1,}.Russ*|Spain a. b. c. d. e.
此查询将匹配任何满足以下条件的标签路径
以标签 Top
开头
然后在前面有零到两个标签
接着是一个以不区分大小写的词缀 sport
开头的标签
然后有一个或多个标签,其中没有一个匹配 football
或 tennis
最后以一个以 Russ
开头或完全匹配 Spain
的标签结尾。
ltxtquery
表示用于匹配 ltree
值的类似全文搜索的模式。ltxtquery
值包含单词,可能在末尾带有修饰符 @
、*
、%
;这些修饰符具有与 lquery
中相同的含义。单词可以用 &
(AND)、|
(OR)、!
(NOT)和括号组合。与 lquery
的主要区别在于,ltxtquery
匹配单词时不考虑它们在标签路径中的位置。
以下是一个 ltxtquery
示例
Europe & Russia*@ & !Transportation
这将匹配包含标签 Europe
和任何以 Russia
(不区分大小写)开头的标签的路径,但不匹配包含标签 Transportation
的路径。这些单词在路径中的位置并不重要。此外,当使用 %
时,无论位置如何,该单词都可以匹配标签中的任何下划线分隔的单词。
注意:ltxtquery
允许符号之间有空格,但 ltree
和 lquery
不允许。
类型 ltree
具有常见的比较运算符 =
、<>
、<
、>
、<=
、>=
。比较按树遍历的顺序进行排序,节点的子节点按标签文本排序。此外,表 F.13 中所示的专门运算符可用。
表 F.13. ltree
运算符
运算符 描述 |
---|
左参数是右参数的祖先(或相等)吗? |
左参数是右参数的后代(或相等)吗? |
|
|
|
连接 |
将文本转换为 |
数组是否包含 |
数组是否包含 |
数组是否包含与 |
|
数组是否包含与 |
返回第一个作为 |
返回第一个作为 |
返回第一个与 |
返回第一个与 |
运算符 <@
、@>
、@
和 ~
具有类似的运算符 ^<@
、^@>
、^@
、^~
,除了不使用索引外,它们是相同的。这些运算符仅用于测试目的。
可用函数显示在 表 F.14 中。
表 F.14. ltree
函数
ltree
支持多种类型的索引,可以加快指定的运算符
B 树索引超过 ltree
:<
、<=
、=
、>=
、>
基于 ltree
的 GiST 索引(gist_ltree_ops
opclass):<
、<=
、=
、>=
、>
、@>
、<@
、@
、~
、?
gist_ltree_ops
GiST opclass 将一组路径标签近似为位图签名。其可选整数参数 siglen
确定签名长度(以字节为单位)。默认签名长度为 8 个字节。长度必须是 int
对齐的正倍数(大多数机器上为 4 个字节),最大为 2024。较长的签名会导致更精确的搜索(扫描索引的较小部分和较少的堆页),但代价是索引更大。
使用默认签名长度 8 个字节创建此类索引的示例
CREATE INDEX path_gist_idx ON test USING GIST (path);
使用签名长度 100 个字节创建此类索引的示例
CREATE INDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100));
基于 ltree[]
的 GiST 索引(gist__ltree_ops
opclass):ltree[] <@ ltree
、ltree @> ltree[]
、@
、~
、?
gist__ltree_ops
GiST opclass 的工作方式类似于 gist_ltree_ops
,并且也采用签名长度作为参数。 gist__ltree_ops
中 siglen
的默认值为 28 个字节。
使用默认签名长度 28 个字节创建此类索引的示例
CREATE INDEX path_gist_idx ON test USING GIST (array_path);
使用签名长度 100 个字节创建此类索引的示例
CREATE INDEX path_gist_idx ON test USING GIST (array_path gist__ltree_ops(siglen=100));
注意:此索引类型有损。
此示例使用以下数据(也可以在源发行版中的文件 contrib/ltree/ltreetest.sql
中找到)
CREATE TABLE test (path ltree); INSERT INTO test VALUES ('Top'); INSERT INTO test VALUES ('Top.Science'); INSERT INTO test VALUES ('Top.Science.Astronomy'); INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics'); INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology'); INSERT INTO test VALUES ('Top.Hobbies'); INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy'); INSERT INTO test VALUES ('Top.Collections'); INSERT INTO test VALUES ('Top.Collections.Pictures'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts'); CREATE INDEX path_gist_idx ON test USING GIST (path); CREATE INDEX path_idx ON test USING BTREE (path);
现在,我们有一个表 test
,其中填充了描述下面所示层次结构的数据
Top / | \ Science Hobbies Collections / | \ Astronomy Amateurs_Astronomy Pictures / \ | Astrophysics Cosmology Astronomy / | \ Galaxies Stars Astronauts
我们可以进行继承
ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science'; path ------------------------------------ Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (4 rows)
以下是路径匹配的一些示例
ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*'; path ----------------------------------------------- Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Collections.Pictures.Astronomy Top.Collections.Pictures.Astronomy.Stars Top.Collections.Pictures.Astronomy.Galaxies Top.Collections.Pictures.Astronomy.Astronauts (7 rows) ltreetest=> SELECT path FROM test WHERE path ~ '*[email protected].*'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (3 rows)
以下是全文搜索的一些示例
ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Hobbies.Amateurs_Astronomy (4 rows) ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (3 rows)
使用函数构建路径
ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy'; ?column? ------------------------------------------ Top.Science.Space.Astronomy Top.Science.Space.Astronomy.Astrophysics Top.Science.Space.Astronomy.Cosmology (3 rows)
我们可以通过创建一个 SQL 函数来简化此操作,该函数在路径中的指定位置插入标签
CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);' LANGUAGE SQL IMMUTABLE; ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy'; ins_label ------------------------------------------ Top.Science.Space.Astronomy Top.Science.Space.Astronomy.Astrophysics Top.Science.Space.Astronomy.Cosmology (3 rows)
ltree_plpython3u
扩展为 PL/Python 中的 ltree
类型实现了转换。如果在创建函数时安装并指定,ltree
值将映射到 Python 列表。(但目前不支持反向转换。)
强烈建议将转换扩展安装在与 ltree
相同的模式中。否则,如果转换扩展的模式包含恶意用户定义的对象,则在安装时存在安全隐患。
所有工作均由 Teodor Sigaev (<[email protected]>
) 和 Oleg Bartunov (<[email protected]>
) 完成。有关更多信息,请参阅 http://www.sai.msu.su/~megera/postgres/gist/。作者要感谢 Eugeny Rodichev 提供的帮助性讨论。欢迎提出评论和错误报告。