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 13默认支持的ltree插件版本为V1.2。

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

• 不同版本的插件支持的功能范围不同，支持使用如下语句查看当前插件的版本：

  select * from pg_available_extensions WHERE name like 'ltree';
  

支持范围

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)