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

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

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

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

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

  • метки данных в иерархической древовидной структуре;

  • расширенные средства для поиска в таких деревьях.

Примечание:

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

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

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

  1. ltree – путь метки.

  2. 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.

    При перечислении нескольких различных меток, отличных от *, через знак |(ИЛИ), можно выбрать любую из меток. Использованием в начале группы без * знака ! (НЕ), можно обозначить необходимость метки, не соответствующей ни одной из списка. Количественное ограничение, если требуется, задается в конце группы, что означает его действие на группу целиком, ограничивая число меток, соответствующих или не соответствующих приведенным в группе.

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

Примечание:

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

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

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

  • =;

  • <>;

  • <;

  • >;

  • <=;

  • >=.

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

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

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

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

  1. B-дерево по значениям ltree:

    • <;

    • <=;

    • =;

    • >=;

    • >.

  2. GiST по значениям ltree (класс операторов gist_ltree_ops):

    • <;

    • <=;

    • =;

    • >=;

    • >;

    • @>;

    • <@;

    • @;

    • ~;

    • ?.

  3. 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.