Postgresql 中文操作指南
F.23. ltree — hierarchical tree-like data type #
此模块实现了一个数据类型 ltree,用于表示存储在树状层次结构中的数据的标签。提供了用于搜索标签树的广泛功能。
This module implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided.
此模块被认为是“受信任的”,也就是说,它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。
This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.
F.23.1. Definitions #
label 是字母数字字符、下划线和连字符的序列。有效的字母数字字符范围取决于数据库语言环境。例如,在 C 语言环境中,允许使用字符 A-Za-z0-9-_。标签长度不得超过 1000 个字符。
A label is a sequence of alphanumeric characters, underscores, and hyphens. Valid alphanumeric character ranges are dependent on the database locale. For example, in C locale, the characters A-Za-z0-9-_ are allowed. Labels must be no more than 1000 characters long.
示例:42、Personal_Services
Examples: 42, Personal_Services
label path 是由点分隔的零个或更多标签序列,例如 L1.L2.L3,表示从层次树的根到特定节点的路径。标签路径的长度不得超过 65535 个标签。
A label path is a sequence of zero or more labels separated by dots, for example L1.L2.L3, representing a path from the root of a hierarchical tree to a particular node. The length of a label path cannot exceed 65535 labels.
示例:Top.Countries.Europe.Russia
Example: Top.Countries.Europe.Russia
ltree 模块提供了多种数据类型:
The ltree module provides several data types:
注意:ltxtquery 允许符号之间有空格,但 ltree 和 lquery 不允许。
Note: ltxtquery allows whitespace between symbols, but ltree and lquery do not.
F.23.2. Operators and Functions #
类型 ltree 具有通常的比较运算符 =、<>、<、>、⇐、>=。按树遍历的顺序进行比较排序,按标签文本对节点的子节点进行排序。此外,还有 Table F.13 中所示的专用运算符。
Type ltree has the usual comparison operators =, <>, <, >, ⇐, >=. Comparison sorts in the order of a tree traversal, with the children of a node sorted by label text. In addition, the specialized operators shown in Table F.13 are available.
Table F.13. ltree Operators
Table F.13. ltree Operators
Operator Description |
ltree @> ltree → boolean Is left argument an ancestor of right (or equal)? |
ltree <@ ltree → boolean Is left argument a descendant of right (or equal)? |
ltree ~ lquery → boolean lquery ~ ltree → boolean Does ltree match lquery? |
ltree ? lquery[] → boolean lquery[] ? ltree → boolean Does ltree match any lquery in array? |
ltree @ ltxtquery → boolean ltxtquery @ ltree → boolean Does ltree match ltxtquery? |
ltree _ |
_ ltree → ltree Concatenates ltree paths. |
ltree _ |
_ text → ltree text _ |
_ ltree → ltree Converts text to ltree and concatenates. |
ltree[] @> ltree → boolean ltree <@ ltree[] → boolean Does array contain an ancestor of ltree? |
ltree[] <@ ltree → boolean ltree @> ltree[] → boolean Does array contain a descendant of ltree? |
ltree[] ~ lquery → boolean lquery ~ ltree[] → boolean Does array contain any path matching lquery? |
ltree[] ? lquery[] → boolean lquery[] ? ltree[] → boolean Does ltree array contain any path matching any lquery? |
ltree[] @ ltxtquery → boolean ltxtquery @ ltree[] → boolean Does array contain any path matching ltxtquery? |
ltree[] ?@> ltree → ltree Returns first array entry that is an ancestor of ltree, or NULL if none. |
ltree[] ?<@ ltree → ltree Returns first array entry that is a descendant of ltree, or NULL if none. |
ltree[] ?~ lquery → ltree Returns first array entry that matches lquery, or NULL if none. |
ltree[] ?@ ltxtquery → ltree Returns first array entry that matches ltxtquery, or NULL if none. |
操作符 <@、@>、@ 和 ~ 有对应的操作符 <@、@>、^@ 和 ^~,除了它们不使用索引外,其他相同。这些操作符仅用于测试目的。
The operators <@, @>, @ and ~ have analogues ^<@, ^@>, ^@, ^~, which are the same except they do not use indexes. These are useful only for testing purposes.
Table F.14中显示可用功能。
The available functions are shown in Table F.14.
Table F.14. ltree Functions
Table F.14. ltree Functions
Function Description Example(s) |
subltree ( ltree, start integer, end integer ) → ltree Returns subpath of ltree from position start to position end-1 (counting from 0). subltree('Top.Child1.Child2', 1, 2) → Child1 |
subpath ( ltree, offset integer, len integer ) → ltree Returns subpath of ltree starting at position offset, with length len. If offset is negative, subpath starts that far from the end of the path. If len is negative, leaves that many labels off the end of the path. subpath('Top.Child1.Child2', 0, 2) → Top.Child1 |
subpath ( ltree, offset integer ) → ltree Returns subpath of ltree starting at position offset, extending to end of path. If offset is negative, subpath starts that far from the end of the path. subpath('Top.Child1.Child2', 1) → Child1.Child2 |
nlevel ( ltree ) → integer Returns number of labels in path. nlevel('Top.Child1.Child2') → 3 |
index ( a ltree, b ltree ) → integer Returns position of first occurrence of b in a, or -1 if not found. index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6') → 6 |
index ( a ltree, b ltree, offset integer ) → integer Returns position of first occurrence of b in a, or -1 if not found. The search starts at position offset; negative offset means start -offset labels from the end of the path. index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6', -4) → 9 |
text2ltree ( text ) → ltree Casts text to ltree. |
ltree2text ( ltree ) → text Casts ltree to text. |
lca ( ltree [, ltree [, … ]] ) → ltree Computes longest common ancestor of paths (up to 8 arguments are supported). lca('1.2.3', '1.2.3.4.5.6') → 1.2 |
lca ( ltree[] ) → ltree Computes longest common ancestor of paths in array. lca(array['1.2.3'::ltree,'1.2.3.4']) → 1.2 |
F.23.3. Indexes #
ltree 支持多类型索引,可以提高指定运算符的速度:
ltree supports several types of indexes that can speed up the indicated operators:
F.23.4. Example #
此示例使用以下数据(也可以在源分布中的文件 contrib/ltree/ltreetest.sql 中找到):
This example uses the following data (also available in file contrib/ltree/ltreetest.sql in the source distribution):
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,其中包含描述以下层次结构的数据:
Now, we have a table test populated with data describing the hierarchy shown below:
Top
/ | \
Science Hobbies Collections
/ | \
Astronomy Amateurs_Astronomy Pictures
/ \ |
Astrophysics Cosmology Astronomy
/ | \
Galaxies Stars Astronauts
我们可以进行继承:
We can do inheritance:
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)
以下是一些路径匹配示例:
Here are some examples of path matching:
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)
以下是全文搜索的一些示例:
Here are some examples of full text search:
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)
使用函数构造路径:
Path construction using functions:
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 函数来简化此操作,该函数会在路径中的指定位置插入标签:
We could simplify this by creating an SQL function that inserts a label at a specified position in a path:
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 列表。(然而,目前不支持相反的操作。)
The ltree_plpython3u extension implements transforms for the ltree type for PL/Python. If installed and specified when creating a function, ltree values are mapped to Python lists. (The reverse is currently not supported, however.)
Caution
强烈建议将转换扩展安装到_ltree_ 相同的模式中。否则,如果转换扩展的模式包含敌对用户定义的对象,则在安装时存在安全隐患。
It is strongly recommended that the transform extension be installed in the same schema as ltree. Otherwise there are installation-time security hazards if a transform extension’s schema contains objects defined by a hostile user.
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 提供的帮助性讨论。欢迎提出意见和错误报告。
All work was done by Teodor Sigaev (<link:mailto:teodor@stack.net[teodor@stack.net]>) and Oleg Bartunov (<link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]>). See http://www.sai.msu.su/megera/postgres/gist/ for additional information. Authors would like to thank Eugeny Rodichev for helpful discussions. Comments and bug reports are welcome.