ltree. Тип данных для представления меток в иерархической древовидной структуре

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Модуль реализует типы данных ltree, lquery, ltxtquery и представляет:

метки данных в иерархической древовидной структуре;
расширенные средства для поиска в таких деревьях.

Примечание:

Метка — это последовательность алфавитно-цифровых символов и знаков подчеркивания длиной до 256 символов. Примеры: 52, Public_Services.

Путь метки — это последовательность из нуля или нескольких разделенных точками меток, представляющая путь от корня иерархического дерева к конкретному узлу. Путь не может содержать больше 65535 меток. Пример: Level1.Level2.Level3.

Модуль предоставляет следующие типы данных:

ltree – путь метки.
lquery – запрос в виде регулярного выражения для поиска нужных значений ltree; в запросе слово выбирает соответствующую метку в заданном пути, а звездочка (*) — ноль или более любых меток.
- Компоненты запроса можно соединить точками для получения в результате всего пути с указанными метками. Например:
```
foo         # Выбирает путь ровно с одной меткой `foo`
*.foo.*     # Выбирает путь, содержащий метку `foo`
*.foo       # Выбирает путь с последней меткой `foo`
```
- Для звездочек и слов можно добавить количественное значение, определяющее число меток, которые будут соответствовать этому компоненту. Например:
```
*{n}        # Выбирает ровно `n` меток
*{n,}       # Выбирает как минимум `n` меток
*{n,m}      # Выбирает не меньше `n`, но и не более `m` меток
*{,m}       # Выбирает не больше `m` меток — равнозначно `*{0,m}`
foo{n,m}    # Выбирает как минимум `n`, но не больше `m` вхождений `foo`
foo{,}      # Выбирает любое количество вхождений `foo`, в том числе ноль
```
- Соответствия при отсутствии явного количественного ограничения:
Компонент запроса Аргумент Описание
* {,} Любое количество меток
слово {1} Ровно одно вхождение
- После элемента lquery, кроме *, могут быть добавлены модификаторы, которые позволяют выбрать более сложные условия:
```
@           # Выбирает совпадение без учета регистра; например, запросу `a@` соответствует `A`
*           # Выбирает любую метку с заданным префиксом, например, запросу `foo*` соответствует `foobar`
%           # Выбирает в метке начальные слова, разделенные подчеркиваниями
```
Модификатор % ищет соответствие по словам, а не по всей метке. Например, запрос foo_bar% выбирает foo_bar_baz, но не foo_barbaz. В сочетании с * сопоставление префикса применяется отдельно к каждому слову, например запрос foo_bar%* выбирает foo1_bar2_baz, но не foo1_br2_baz.

При перечислении нескольких различных меток, отличных от *, через знак |(ИЛИ), можно выбрать любую из меток. Использованием в начале группы без * знака ! (НЕ), можно обозначить необходимость метки, не соответствующей ни одной из списка. Количественное ограничение, если требуется, задается в конце группы, что означает его действие на группу целиком, ограничивая число меток, соответствующих или не соответствующих приведенным в группе.
ltxtquery – представляет подобный полнотекстовому запрос поиска подходящих значений ltree. Значение ltxtquery содержит слова, возможно с модификаторами @, *, % в конце; эти модификаторы имеют то же значение, что и в lquery. Слова можно объединять символами & (И), | (ИЛИ), ! (НЕ) и скобками. Отличается от lquery тем, что ltxtquery выбирает слова независимо от их положения в пути метки.

Компонент запроса	Аргумент	Описание
`*`	`{,}`	Любое количество меток
`слово`	`{1}`	Ровно одно вхождение

Примечание:

ltxtquery допускает пробелы между символами.

ltree, lquery — не допускают пробелы между символами.

Для типа ltree определены простые операторы сравнения:

=;
<>;
<;
>;
<=;
>=.

Сравнение сортирует пути в порядке движения по дереву, а потомки узла сортируются по тексту метки.

Модуль предоставляет специализированные операторы и функции.

Перечень и подробное описание специализированных операторов и функций: https://www.postgresql.org/docs/15/ltree.html#id-1.11.7.32.6

Модуль поддерживает следующие типы индексов для ускорения выполняемых операций:

B-дерево по значениям ltree:
- <;
- <=;
- =;
- >=;
- >.
GiST по значениям ltree (класс операторов gist_ltree_ops):
- <;
- <=;
- =;
- >=;
- >;
- @>;
- <@;
- @;
- ~;
- ?.
GiST-индекс по массиву ltree[] (класс операторов gist_ltree_ops):
- ltree[] <@ ltree;
- ltree @> ltree[];
- @;
- ~;
- ?.

Подробное описание индексов ltree: https://www.postgresql.org/docs/15/ltree.html#id-1.11.7.32.7.

Существуют дополнительные расширения, реализующие трансформации типа ltree для языка PL/Python:

Если установить эти трансформации и указать их при создании функции, значения ltree будут отображаться в словаре Python. Обратное преобразование не поддерживается.

Доработка

Доработка не проводилась.

Ограничения

Ограничения отсутствуют.

Установка

Модуль считается «доверенным», поэтому его могут устанавливать пользователи, имеющие право CREATE в текущей базе данных:

CREATE EXTENSION ltree SCHEMA ext;

Внимание!

Расширения, реализующие трансформации, рекомендуется устанавливать в одну схему с ltree. Другая схема может содержать объекты, созданные злонамеренным пользователем. Выбор такой схемы может повлечь за собой угрозу безопасности во время установки расширения.

Настройка

Настройка не требуется.

Использование модуля

Для примера приняты данные из дистрибутива исходного кода – файл contrib/ltree/ltreetest.sql.

Создать таблицу, вставить тестовые данные и создать два индекса:

CREATE TABLE test (path ltree);
INSERT INTO test 
    VALUES  ('Top'),
            ('Top.Science'),
            ('Top.Science.Astronomy'),
            ('Top.Science.Astronomy.Astrophysics'),
            ('Top.Science.Astronomy.Cosmology'),
            ('Top.Hobbies'),
            ('Top.Hobbies.Amateurs_Astronomy'),
            ('Top.Collections'),
            ('Top.Collections.Pictures'),
            ('Top.Collections.Pictures.Astronomy'),
            ('Top.Collections.Pictures.Astronomy.Stars'),
            ('Top.Collections.Pictures.Astronomy.Galaxies'),
            ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);

Результат:

                                    Table "ext.test"
 Column | Type  | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-------+-----------+----------+---------+----------+--------------+-------------
 path   | ltree |           |          |         | extended |              | 
Indexes:
    "path_gist_idx" gist (path)
    "path_idx" btree (path)
Access method: heap

В итоге получатся таблица test c данными, представляющими следующую иерархию:

                        Top
                     /   |  \
             Science Hobbies Collections
                 /       |              \
        Astronomy   Amateurs_Astronomy Pictures
           /  \                            |
Astrophysics  Cosmology                Astronomy
                                        /  |    \
                                 Galaxies Stars Astronauts

Выбор потомков в иерархии наследования:

SELECT path FROM test WHERE path <@ 'Top.Science';

Пример вывода:

                path                
------------------------------------
 Top.Science
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(4 rows)

Примеры выборки по путям:

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)

SELECT path FROM test WHERE path ~ '*.!pictures@.Astronomy.*';

Пример вывода:

                path                
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

Примеры полнотекстового поиска:

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)

SELECT path FROM test WHERE path @ 'Astro* & !pictures@';

Пример вывода:

                path                
------------------------------------
 Top.Science.Astronomy
 Top.Science.Astronomy.Astrophysics
 Top.Science.Astronomy.Cosmology
(3 rows)

Образование пути с помощью функций:

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;

Использование созданной функции:

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)

Ссылки на документацию разработчика

Дополнительно поставляемый модуль ltree: https://www.postgresql.org/docs/15/ltree.html.

Доработка​

Ограничения​

Установка​

Настройка​

Использование модуля​

Ссылки на документацию разработчика​

Доработка

Ограничения

Установка

Настройка

Использование модуля

Ссылки на документацию разработчика