F.21. ltree · PostgreSQL 中文文档 9.3

# F.21\. ltree 这个模块实现了数据类型`ltree`，该类型表示存储在分层的树形结构里的数据的标签。提供大量的工具搜索标签树。 ## F.21.1\. 定义 _label_是一个字母数字字符和下划线的序列（例如，在C语言环境，字符`A-Za-z0-9_`是允许的）。标签长度必须少于256字节。示例：`42`, `Personal_Services` _label path_是零个或多个由点分隔的标签序列，例如`L1.L2.L3`，表示由树状结构的根节点到具体节点的路径。标签路径的长度必须少于65Kb，但是保持在2Kb以下是最好的。实际上，这不是主要限制；例如，在DMOZ目录中的最长标签路径([http://www.dmoz.org](http://www.dmoz.org))大约240字节。示例：`Top.Countries.Europe.Russia` `ltree`模块提供几种数据类型： * `ltree`存储标签路径。 * `lquery`表示一个正规表达式类似的模式以匹配`ltree`值。在一个路径中一个单词匹配那个标签。星号(`*`)匹配零个或多个标签。例如： ``` foo 准确匹配标签路径 `foo` *.foo.* 匹配任何包含标签 `foo` 的标签路径 *.foo 匹配任何以 `foo` 结束的标签路径 ``` 可以量化星号来限制可以匹配多少个标签： ``` *{n} 准确匹配 n 个标签 *{n,} 至少匹配 n 个标签 *{n,m} 至少匹配 n 个但不超过 m 个标签 *{,m} 最多匹配 m 个标签 — 和 *{0,m 相同} ``` 有几个修饰符可以放在`lquery`中非星号标签的后面，使其不只是匹配正好的那个： ``` @ 匹配大小写无关，例如 a@ 匹配 `A` * 匹配任何带有这个前缀的标签，例如 foo* 匹配 foobar % 匹配字首下划线分开的单词 ``` `%`的行为稍微有点复杂。它试图匹配单词而不是整个标签。例如`foo_bar%` 匹配 `foo_bar_baz`而不是`foo_barbaz`。如果与`*`结合，前缀匹配分别应用到每个单词，例如`foo_bar%*`匹配`foo1_bar2_baz` 而不是not `foo1_br2_baz`。还有，可以写几个由`|` (OR)分开的标签以匹配任意这些标签，并且可以在开始放置`!` (NOT)以匹配任何不匹配任何可选标签的标签。这是一个带有评注的`lquery`的例子： ``` Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain a. b. c. d. e. ``` 这个查询将匹配任何标签路径： 1. 以标签`Top`开始 2. 然后有0到2个标签 3. 然后是一个带有大小写不敏感前缀`sport`的标签 4. 然后是一个既不匹配`football`也不匹配`tennis`的标签 5. 然后以一个带有`Russ`或准确匹配`Spain`开头的标签作为结束。 * `ltxtquery`表示一个匹配`ltree`值的全文本搜索风格模式。 `ltxtquery`值包含单词，可能在结尾带有修饰词`@`, `*`, `%`，这些修饰词的含义和在`lquery`中的含义相同。单词可以与`&` (AND), `|` (OR), `!` (NOT) 和圆括号结合。与`lquery` 主要的不同是`ltxtquery`匹配单词时不考虑单词在标签路径中的位置。这是一个`ltxtquery`的例子： ``` Europe & Russia*@ & !Transportation ``` 这将匹配包含`Europe`标签和以`Russia` （大小写不敏感）开始的标签的路径，但是不匹配包含`Transportation` 标签的路径。这些单词在路径中的位置并不重要。还有，当使用了`%`时，该单词能匹配标签中任何用下划线分隔单词，不管位置在哪。注意：`ltxtquery`允许符号之间有空格，但是`ltree` 和 `lquery`不允许这样做。 ## F.21.2\. 操作符和函数类型`ltree`有平常的比较操作符`=`, `<>`, `<`, `>`, `<=`, `>=`。比较的优先级以树遍历的顺序，节点的子节点以标签文本排序。另外，在[Table F-12](#calibre_link-1436)中显示的专业的操作符是可用的。 **Table F-12\. `ltree` 操作符** | 操作符 | 返回 | 描述 | | --- | --- | --- | | `ltree` `@>` `ltree` | `boolean` | 左边参数是右边参数的上代 (或相等)? | | `ltree` `<@` `ltree` | `boolean` | 左边参数是右边参数的后代 (或相等)? | | `ltree` `~` `lquery` | `boolean` | `ltree` 匹配 `lquery`? | | `lquery` `~` `ltree` | `boolean` | `ltree` 匹配 `lquery`? | | `ltree` `?` `lquery[]` | `boolean` | `ltree` 匹配数组中的任意 `lquery`? | | `lquery[]` `?` `ltree` | `boolean` | `ltree` 匹配数组中的任意 `lquery`? | | `ltree` `@` `ltxtquery` | `boolean` | `ltree` 匹配 `ltxtquery`? | | `ltxtquery` `@` `ltree` | `boolean` | `ltree` 匹配 `ltxtquery`? | | `ltree` `||` `ltree` | `ltree` | 连接 `ltree` 路径 | | `ltree` `||` `text` | `ltree` | 将文本转化成 `ltree` 并连接 | | `text` `||` `ltree` | `ltree` | 将文本转化成 `ltree` 并连接 | | `ltree[]` `@>` `ltree` | `boolean` | 数组包含 `ltree` 的祖先? | | `ltree` `<@` `ltree[]` | `boolean` | 数组包含 `ltree` 的祖先? | | `ltree[]` `<@` `ltree` | `boolean` | 数组包含 `ltree` 的后代? | | `ltree` `@>` `ltree[]` | `boolean` | 数组包含 `ltree` 的后代? | | `ltree[]` `~` `lquery` | `boolean` | 数据包含任何匹配 `lquery` 的路径? | | `lquery` `~` `ltree[]` | `boolean` | 数据包含任何匹配 `lquery` 的路径? | | `ltree[]` `?` `lquery[]` | `boolean` | `ltree` 数组包含任意路径匹配任意 `lquery`? | | `lquery[]` `?` `ltree[]` | `boolean` | `ltree` 数组包含任意路径匹配任意 `lquery`? | | `ltree[]` `@` `ltxtquery` | `boolean` | 数据包含任意路径匹配 `ltxtquery`? | | `ltxtquery` `@` `ltree[]` | `boolean` | 数组包含任意路径匹配 `ltxtquery`? | | `ltree[]` `?@>` `ltree` | `ltree` | 第一个数组条目是 `ltree` 的祖先？; 如果不是则为 NULL | | `ltree[]` `?<@` `ltree` | `ltree` | 第一个数组条目是 `ltree` 的后代？; 如果不是则为 NULL | | `ltree[]` `?~` `lquery` | `ltree` | 第一个数组条目匹配 `lquery` ？; 如果不是则为 NULL | | `ltree[]` `?@` `ltxtquery` | `ltree` | 第一个数组条目匹配 `ltxtquery` ？; 如果不是则为 NULL | 操作符 `<@`, `@>`, `@` 和 `~` 类似 `^<@`, `^@>`, `^@`, `^~`，除了不用索引之外都相同。这些只是对于测试目的有用。可用的函数显示在 [Table F-13](#calibre_link-1437) 中。 **Table F-13\. `ltree` 函数** | 函数 | 返回类型 | 描述 | 示例 | 结果 | | --- | --- | --- | --- | --- | | `subltree(ltree, int start, int end)` | `ltree` | `ltree`的子路径，从位置 `start` 到位置 `end`-1 (从0开始计算) | `subltree('Top.Child1.Child2',1,2)` | `Child1` | | `subpath(ltree, int offset, int len)` | `ltree` | `ltree` 的子路径，从位置 `offset` 开始，长度为 `len`。如果 `offset` 为负值，子路径从路径的结尾开始。如果 `len` 是负值，那么距离路径的结尾多少个标签。 | `subpath('Top.Child1.Child2',0,2)` | `Top.Child1` | | `subpath(ltree, int offset)` | `ltree` | `ltree` 的子路径，从位置 `offset` 开始，一直到路径的结束。如果 `offset` 为负值，那么子路径从路径的结尾开始。 | `subpath('Top.Child1.Child2',1)` | `Child1.Child2` | | `nlevel(ltree)` | `integer` | 路径中的标签的个数 | `nlevel('Top.Child1.Child2')` | `3` | | `index(ltree a, ltree b)` | `integer` | `b` 在 `a` 中第一次出现的位置；如果没有发现则为 -1 | `index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')` | `6` | | `index(ltree a, ltree b, int offset)` | `integer` | `b` 在 `a` 中第一次出现的位置，从 `offset` 开始搜索；负的 `offset` 意味从路径结尾开始 `-offset` 个标签 | `index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)` | `9` | | `text2ltree(text)` | `ltree` | 将 `text` 转换为 `ltree` | | `ltree2text(ltree)` | `text` | 将 `ltree` 转换为 `text` | | `lca(ltree, ltree, ...)` | `ltree` | 最低的公共祖先，也就是，路径的最长公共前缀（支持多达 8 个参数） | `lca('1.2.2.3','1.2.3.4.5.6')` | `1.2` | | `lca(ltree[])` | `ltree` | 最低的公共祖先，也就是，路径的最长公共前缀 | `lca(array['1.2.2.3'::ltree,'1.2.3'])` | `1.2` | ## F.21.3\. 索引 `ltree` 支持索引的几个类型，可以加速指出的操作符： * `ltree` 上的 B-tree 索引： `<`, `<=`, `=`, `>=`, `>` * `ltree` 上的 GiST 索引： `<`, `<=`, `=`, `>=`, `>`, `@>`, `<@`, `@`, `~`, `?` 创建这样一个索引的列子： ``` CREATE INDEX path_gist_idx ON test USING GIST (path); ``` * `ltree[]` 上的 GiST 索引： `ltree[] <@ ltree`, `ltree @> ltree[]`, `@`, `~`, `?` 创建这样一个索引的例子： ``` CREATE INDEX path_gist_idx ON test USING GIST (array_path); ``` 注意：这种索引类型损耗很大。 ## F.21.4\. 示例这个示例使用下列的数据（在源代码发布中的`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 ~ '*.!pictures@.*.Astronomy.*'; 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) ``` ## F.21.5\. 作者所有工作都是 Teodor Sigaev (`<[teodor@stack.net](mailto:teodor@stack.net)>`) 和 Oleg Bartunov (`<[oleg@sai.msu.su](mailto:oleg@sai.msu.su)>`)做的。参阅 [http://www.sai.msu.su/~megera/postgres/gist/](http://www.sai.msu.su/~megera/postgres/gist/)获取额外的信息。作者感谢 Eugeny Rodichev 的有帮助的讨论。欢迎评论和报告bug。