ltree

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 附录 F. 额外提供的模块 | 下一页

# 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)大约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`有平常的比较操作符`=`, `<>`, `<`, `>`, `<=`, `>=`。 比较的优先级以树遍历的顺序，节点的子节点以标签文本排序。另外， 在表 F-12中显示的专业的操作符是可用的。

表 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

操作符 `<@`, `@>`, `@` 和 `~` 类似 `^<@`, `^@>`, `^@`, `^~`，除了不用索引之外都相同。这些只是对于测试目的有用。

可用的函数显示在 表 F-13 中。

表 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>`) 和 Oleg Bartunov (`<oleg@sai.msu.su>`)做的。参阅 http://www.sai.msu.su/~megera/postgres/gist/获取额外的信息。 作者感谢 Eugeny Rodichev 的有帮助的讨论。欢迎评论和报告bug。

上一页 | 起始页 | 下一页
lo | 上一级 | pageinspect