Postgresql 中文操作指南

F.23. ltree — hierarchical tree-like data type #

此模块实现了一个数据类型 ltree，用于表示存储在树状层次结构中的数据的标签。提供了用于搜索标签树的广泛功能。

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

F.23.1. Definitions #

label 是字母数字字符、下划线和连字符的序列。有效的字母数字字符范围取决于数据库语言环境。例如，在 C 语言环境中，允许使用字符 A-Za-z0-9-_。标签长度不得超过 1000 个字符。

示例：42、Personal_Services

label path 是由点分隔的零个或更多标签序列，例如 L1.L2.L3，表示从层次树的根到特定节点的路径。标签路径的长度不得超过 65535 个标签。

示例：Top.Countries.Europe.Russia

ltree 模块提供了多种数据类型：

注意：ltxtquery 允许符号之间有空格，但 ltree 和 lquery 不允许。

F.23.2. Operators and Functions #

类型 ltree 具有通常的比较运算符 =、<>、<、>、⇐、>=。按树遍历的顺序进行比较排序，按标签文本对节点的子节点进行排序。此外，还有 Table F.13 中所示的专用运算符。

Table F.13. ltree Operators

Operator

Description

ltree @&gt; ltree → boolean 左参数是否是右参数的祖先（或相等）？

ltree &lt;@ ltree → boolean 左参数是否是右参数的后代（或相等）？

ltree ~ lquery → boolean lquery ~ ltree → boolean ltree 是否匹配 lquery ？

ltree ? lquery[] → boolean lquery[] ? ltree → boolean ltree 是否匹配数组中的任何 lquery ？

ltree @ ltxtquery → boolean ltxtquery @ ltree → boolean ltree 是否匹配 ltxtquery ？

ltree _

_ ltree → ltree 连接 ltree 路径。

ltree _

_ text → ltree text _

_ ltree → ltree 将文本转换为 ltree 并连接。

ltree[] @&gt; ltree → boolean ltree &lt;@ ltree[] → boolean 数组是否包含 ltree 的祖先？

ltree[] &lt;@ ltree → boolean ltree @&gt; ltree[] → boolean 数组是否包含 ltree 的后代？

ltree[] ~ lquery → boolean lquery ~ ltree[] → boolean 数组是否包含与 lquery 匹配的任何路径？

ltree[] ? lquery[] → boolean lquery[] ? ltree[] → boolean ltree 数组是否包含与任何 lquery 匹配的任何路径？

ltree[] @ ltxtquery → boolean ltxtquery @ ltree[] → boolean 阵列是否包含与 ltxtquery 匹配的任何路径？

ltree[] ?@&gt; ltree → ltree 返回第一个为 ltree 的祖先的阵列条目，如果没有则返回 NULL 。

ltree[] ?&lt;@ ltree → ltree 返回第一个为 ltree 的后代的阵列条目，如果没有则返回 NULL 。

ltree[] ?~ lquery → ltree 返回第一个与 lquery 匹配的阵列条目，如果没有则返回 NULL 。

ltree[] ?@ ltxtquery → ltree 返回第一个与 ltxtquery 匹配的阵列条目，如果没有则返回 NULL 。

操作符 <@、@>、@ 和 ~ 有对应的操作符 <@、@>、^@ 和 ^~，除了它们不使用索引外，其他相同。这些操作符仅用于测试目的。

Table F.14中显示可用功能。

Table F.14. ltree Functions

Function

Description

Example(s)

subltree ( ltree , start integer , end integer ) → ltree 从位置 start 返回 ltree 的子路径到位置 end -1（从 0 开始计数）。 subltree('Top.Child1.Child2', 1, 2) → Child1

subpath ( ltree , offset integer , len integer ) → ltree 从位置 offset 开始返回 ltree 的子路径，长度为 len 。如果 offset 为负值，则子路径从路径末尾开始。如果 len 为负值，则从路径末尾除去许多标签。 subpath('Top.Child1.Child2', 0, 2) → Top.Child1

subpath ( ltree , offset integer ) → ltree 从位置 offset 开始返回 ltree 的子路径，扩展到路径末尾。如果 offset 为负值，则子路径从路径末尾开始。 subpath('Top.Child1.Child2', 1) → Child1.Child2

nlevel ( ltree ) → integer 返回路径中的标签数。 nlevel('Top.Child1.Child2') → 3

index ( a ltree , b ltree ) → integer 返回 b 在 a 中首次出现的位置，如果未找到则返回 index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6') 。 index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6') → 6

index ( a ltree , b ltree , offset integer ) → integer 返回 b 在 a 中首次出现的位置，如果未找到则返回 -1。搜索从位置 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.3', '1.2.3.4.5.6') → 1.2

lca ( ltree[] ) → ltree 计算阵列中路径的最长公共祖先。 lca(array['1.2.3'::ltree,'1.2.3.4']) → 1.2

F.23.3. Indexes #

ltree 支持多类型索引，可以提高指定运算符的速度：

F.23.4. Example #

此示例使用以下数据（也可以在源分布中的文件 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.23.5. Transforms #

ltree_plpython3u 扩展实现了 ltree 类型对于 PL/Python 的转换。如果在创建函数之时已安装并指定了它，ltree 值将被映射到 Python 列表。（然而，目前不支持相反的操作。）

Caution

强烈建议将转换扩展安装到_ltree_ 相同的模式中。否则，如果转换扩展的模式包含敌对用户定义的对象，则在安装时存在安全隐患。

F.23.6. Authors #

所有工作均由 Teodor Sigaev ( < link:mailto:teodor@stack.net[teodor@stack.net]> ) 和 Oleg Bartunov ( < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> ) 完成。有关更多信息，请参见链接：http://www.sai.msu.su/ megera/postgres/gist/[http://www.sai.msu.su/ megera/postgres/gist/]。作者要感谢 Eugeny Rodichev 提供的帮助性讨论。欢迎提出意见和错误报告。