ltree插件

功能描述

ltree插件实现了一种数据类型，用于表示存储在一个层次树状结构中的数据的标签，并且提供了在标签树中搜索的扩展功能。

• 标签是由字符组成的序列。在C区域中允许标签使用以下字符：大写字母（A-Z）、小写字母（a-z）、数字（0-9）、连字符（-）、下划线（_）。标签支持的字符与数据库当前的区域设置有关。标签长度必须少于1000字符。

  支持在初始化数据库时通过--locale选项指定区域。如果不加--locale选项，则采用系统默认的区域。系统默认区域和编码格式可以使用locale如下命令查看：

  locale|grep LC_CTYPE
  

• 一个标签路径是由点号分隔的零个或者更多个标签的序列，例如L1.L2.L3，它表示一个从层次树的根到一个特定节点的路径。一个标签路径的长度必须小于65535个标签。

注意事项

• 支持版本：

  数据库版本	支持情况
  Vastbase G100 V2.2 Build 10 (Patch No.6)及以下	不支持
  Vastbase G100 V2.2 Build 10 (Patch No.7)及以上	ltree V1.2
  Vastbase G100 V2.2 Build 11、V2.2 Build 12	ltree V1.0
  Vastbase G100 V2.2 Build 13及以上	ltree V1.2

• 支持使用如下语句查看当前插件的版本：

  select * from pg_available_extensions WHERE name like 'ltree';
  

• 创建此扩展要求用户在当前数据库中具有create权限。

支持范围

ltree插件提供了多种数据类型：

• ltree存储一个标签路径。

  例如：

  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');
  

• lquery表示一个用于匹配ltree值的类正则表达式。

  一个简单词可以匹配一个路径中的标签，星号可以匹配零个或多个标签。星号和简单词都可以被限定来限制它能匹配多少标签。它们可以用点连接起来，以形成一个必须匹配整个标签路径的模式。例如：

  模式	描述
  foo	正好匹配标签路径foo。
  *.foo.*	匹配任何包含标签foo的路径。
  *.foo	匹配任何最后一个标签是foo的标签路径。
  *{n}	匹配正好n个标签。
  *{n, }	匹配至少n个标签。
  *{n,m}	匹配至少n个最多m个标签。
  *{,m}	匹配至多m个标签。
  foo{n,m}	匹配至少n个最多m个foo标签。
  foo{,}	匹配任意数量的foo标签。

  在lquery中，可以在一个非星号标签的末尾用如下的修饰符，使它能匹配除了精确匹配之外更多的匹配:：

  修饰符	描述
  @	不区分大小写匹配，例如a@匹配A。
  *	匹配带此前缀的任何标签，例如foo*匹配foobar。
  %	匹配开头以下划线分隔的词。

  除此之外，还可以使用 |(OR)、! (NOT)修饰符进行匹配。

• ltxtquery表示一种用于匹配ltree值的类全文搜索的模式。

  一个ltxtquery值包含词，也可能在末尾带有修饰符@、*、%，修饰符具有和lquery中相同的含义。词可以用&（AND）、|（OR）、!（NOT）以及圆括号组合。例如：

  CREATE TABLE test (path ltree);
  INSERT INTO test VALUES ('Top.Science');
  SELECT path~'Science'::lquery from test;  --返回f
  SELECT path@'Science'::ltxtquery from test;   --返回t
  

  或者：

  Europe & Russia*@ & !Transportation
  

  上述语句表示匹配包含标签Europe以及任何以Russia开始（大小写不敏感）的标签的路径，但是不匹配包含标签Transportation的路径。这些词在路径中的位置并不重要。

• ltxtquery允许符号之间存在空白，但是ltree和lquery不允许。
• lquery和ltxtquery的关键区别是后者匹配词时不考虑它们在标签路径中的位置。

操作符

ltree支持普通比较操作符=、<>、<、>、<=、>=。比较会按照树遍历的顺序排序，一个节点的子女按照标签文本排序。除此以外，还支持下表列出的特殊操作符：

函数

索引

ltree支持一些能加速上述操作符的索引类型：

• ltree数据类型支持B-树索引：<、<=、=、>=、>
• ltree数据类型支持GIST索引：<、<=、=、>=、>、@>、<@、@、~、?
• ltree[]数据类型支持GIST索引：ltree[]<@ltree、ltree@>ltree[]、@、~、?

在PG数据库中ltree的gist索引在使用时可以加选项带参数，比如：

create index tstidx on treetest using gist (t gist_ltree _ops(siglen=2025))

Vastbase不支持该功能，在Vastbase数据库中使用方法如下：

create index tstidx on ltreetest using gist (t gist_ltree_ops);

示例

示例1： ltree数据类型的列支持增删改查。

1、创建ltree插件。

create extension ltree;

2、创建包含数据类型ltree的表。

create table tb_1101588(id int, path ltree);

3、向表中插入数据。

insert into tb_1101588 values(1, 'GangTai.NanGeShou.ZhouJieLun.QiLiXiang');
insert into tb_1101588 values(2, 'GangTai.NanGeShou.ZhouJieLun.QingTian');
insert into tb_1101588 values(3, 'NeiDi.NanGeShou');
insert into tb_1101588 values(4, 'NeiDi.NvGeShou.ZhangLiangYing');
insert into tb_1101588 values(5, 'NeiDi');

4、查询表中得数据。

select * from tb_1101588;

返回结果为：

 id |                  path
----+----------------------------------------
  1 | GangTai.NanGeShou.ZhouJieLun.QiLiXiang
  2 | GangTai.NanGeShou.ZhouJieLun.QingTian
  3 | NeiDi.NanGeShou
  4 | NeiDi.NvGeShou.ZhangLiangYing
  5 | NeiDi
(5 rows)

5、更新表中数据。

update tb_1101588 set path = 'GangTai.NanGeShou.LinJunJie' where path ~ '*.QingTian';

查询数据：

select * from tb_1101588;

返回结果为：

 id |                  path
----+----------------------------------------
  1 | GangTai.NanGeShou.ZhouJieLun.QiLiXiang
  3 | NeiDi.NanGeShou
  4 | NeiDi.NvGeShou.ZhangLiangYing
  5 | NeiDi
  2 | GangTai.NanGeShou.LinJunJie
(5 rows)

6、删除表中数据。

delete from tb_1101588 where path ~ '*.QiLiXiang';

7、查询表中数据。

select * from tb_1101588;

返回结果为：

 id |             path
----+-------------------------------
  3 | NeiDi.NanGeShou
  4 | NeiDi.NvGeShou.ZhangLiangYing
  5 | NeiDi
  2 | GangTai.NanGeShou.LinJunJie
(4 rows)

8、删除测试表。

drop table tb_1101588;

示例2： 数据类型lquery匹配ltree值的类正则表达式（foo正好匹配标签路径foo）。

1、创建测试表。

create table tb_1101393(id int, path ltree);

2、向表中插入数据。

insert into tb_1101393 values(1, 'GangTai.NanGeShou');
insert into tb_1101393 values(2, 'GangTai.NvGeShou');

3、匹配标签路径。

select path from tb_1101393 where path ~ 'GangTai.NvGeShou';

返回结果为：

       path
------------------
 GangTai.NvGeShou
(1 row)

4、删除测试表。

drop table tb_1101393;

示例3： 数据类型lquery匹配ltree值的类正则表达式（*.foo.*匹配任何包含标签foo的标签路径）。

1、创建测试表。

create table tb_1101394(id int, path ltree);

2、向表中插入数据。

insert into tb_1101394 values(1, 'GangTai.NanGeShou.ZhouJieLun.QiLiXiang');
insert into tb_1101394 values(2, 'GangTai.NvGeShou.CaiYiLin.RiBuLuo.Test');
insert into tb_1101394 values(3, 'GangTai.NvGeShou.CaiYiLin.GuaiMeiDe');

3、匹配标签路径。

select path from tb_1101394 where path ~ '*.CaiYiLin.*';

返回结果为：

                  path
----------------------------------------
 GangTai.NvGeShou.CaiYiLin.RiBuLuo.Test
 GangTai.NvGeShou.CaiYiLin.GuaiMeiDe
(2 rows)

4、删除测试表。

drop table tb_1101394;

示例4： 操作符ltree[] ~ lquery和lquery ~ ltree[]（判断数组是否包含匹配lquery的路径）。

select '{"NeiDi", "GangTai"}'::ltree[] ~ 'NeiDi*';

返回结果为：

 ?column?
----------
 t
(1 row)

示例5： 调用函数index(a ltree,b ltree)返回a中第一次出现b的位置，没有找到则返回-1，从0开始。

1、创建表中数据类型为ltree。

create table tb_1101506(id int, path ltree);

2、向表中插入数据。

insert into tb_1101506 values(1, 'GangTai.NanGeShou.ZhouJieLun.QiLiXiang');
insert into tb_1101506 values(2, 'GangTai.NanGeShou.ZhouJieLun.QingTian');
insert into tb_1101506 values(3, 'NeiDi.NanGeShou');
insert into tb_1101506 values(4, 'NeiDi.NvGeShou.ZhangLiangYing');
insert into tb_1101506 values(5, 'NeiDi');

3、调用index函数。

select index(path, 'NanGeShou'::ltree) from tb_1101506;

返回结果为：

 index
-------
     1
     1
     1
    -1
    -1
(5 rows)

4、删除表。

drop table tb_1101506;

示例6： 调用函数lca(ltree[])计算数组中的路径的最长公共祖先。

select lca(array['1.2.3'::ltree, '1.2.3.4']);

返回结果为：

 lca
-----
 1.2
(1 row)

示例7： ltree上的B-树索引。

1、创建表中数据类型为ltree。

create table tb_1101523(id int, path ltree);

2、向表中插入数据。

insert into tb_1101523 values(1, 'GangTai.NanGeShou.ZhouJieLun.QiLiXiang');
insert into tb_1101523 values(2, 'GangTai.NanGeShou.ZhouJieLun.QingTian');
insert into tb_1101523 values(3, 'NeiDi.NanGeShou');
insert into tb_1101523 values(4, 'NeiDi.NvGeShou.ZhangLiangYing');
insert into tb_1101523 values(5, 'NeiDi');

3、创建索引。

create index idx_1101523 on tb_1101523(path);

4、验证操作符<、<=、=、>=、>。

select path from tb_1101523 where path < 'NeiDi.NvGeShou';

返回结果分别为：

                  path
----------------------------------------
 GangTai.NanGeShou.ZhouJieLun.QiLiXiang
 GangTai.NanGeShou.ZhouJieLun.QingTian
 NeiDi.NanGeShou
 NeiDi
(4 rows)

5、验证操作符<=。

select path from tb_1101523 where path <= 'NeiDi.NvGeShou';

返回结果为：

                  path
----------------------------------------
 GangTai.NanGeShou.ZhouJieLun.QiLiXiang
 GangTai.NanGeShou.ZhouJieLun.QingTian
 NeiDi.NanGeShou
 NeiDi
(4 rows)

6、验证操作符=。

select path from tb_1101523 where path = 'NeiDi';

返回结果为：

 path
-------
 NeiDi
(1 row)

7、验证操作符>=。

select path from tb_1101523 where path >= 'NeiDi.NvGeShou';

返回结果为：

             path
-------------------------------
 NeiDi.NvGeShou.ZhangLiangYing
(1 row)

8、验证操作符>。

select path from tb_1101523 where path > 'NeiDi.NvGeShou';

返回结果为：

             path
-------------------------------
 NeiDi.NvGeShou.ZhangLiangYing
(1 row)

9、关闭顺序扫描，验证查询时走索引扫描。

set enable_seqscan =off;
set enable_bitmapscan =off;
explain select path from tb_1101523 where path < 'NeiDi.NvGeShou';

返回结果为：

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Only Scan using idx_1101523 on tb_1101523  (cost=0.00..8.27 rows=1 width=32)
   Index Cond: (path < 'NeiDi.NvGeShou'::ltree)
(2 rows)