ltree

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

1. ltree – путь метки.
2. lquery – запрос в виде регулярного выражения для поиска нужных значений ltree; в запросе слово выбирает соответствующую метку в заданном пути, а звездочка (*) — ноль или более любых меток.
   • Компоненты запроса можно соединить точками для получения в результате всего пути с указанными метками. Например:

     Раскрыть type=bash
     foo         # Выбирает путь ровно с одной меткой `foo`
     *.foo.*     # Выбирает путь, содержащий метку `foo`
     *.foo       # Выбирает путь с последней меткой `foo`
     

   • Для звездочек и слов можно добавить количественное значение, определяющее число меток, которые будут соответствовать этому компоненту. Например:

     Раскрыть type=bash
     *{n}        # Выбирает ровно `n` меток
     *{n,}       # Выбирает как минимум `n` меток
     *{n,m}      # Выбирает не меньше `n`, но и не более `m` меток
     *{,m}       # Выбирает не больше `m` меток — равнозначно `*{0,m}`
     foo{n,m}    # Выбирает как минимум `n`, но не больше `m` вхождений `foo`
     foo{,}      # Выбирает любое количество вхождений `foo`, в том числе ноль
     

   • Соответствия при отсутствии явного количественного ограничения:

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

   • После элемента lquery, кроме *, могут быть добавлены модификаторы, которые позволяют выбрать более сложные условия:

     Раскрыть type=bash
     @           # Выбирает совпадение без учета регистра; например, запросу `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 — не допускают пробелы между символами.

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

• ltree_plpythonu;
• ltree_plpython2u;
• ltree_plpython3u.

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

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

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

   Результат:

   Раскрыть type=sql
                                       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
   

2. Выбор потомков в иерархии наследования:
   SELECT path FROM test WHERE path <@ 'Top.Science';
   Пример вывода:

   Раскрыть type=sql
                   path
   ------------------------------------
    Top.Science
    Top.Science.Astronomy
    Top.Science.Astronomy.Astrophysics
    Top.Science.Astronomy.Cosmology
   (4 rows)
   

3. Примеры выборки по путям:
   SELECT path FROM test WHERE path ~ '*.Astronomy.*';
   Пример вывода:

   Раскрыть type=sql
                        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.*';
   Пример вывода:

   Раскрыть type=sql
                   path
   ------------------------------------
    Top.Science.Astronomy
    Top.Science.Astronomy.Astrophysics
    Top.Science.Astronomy.Cosmology
   (3 rows)
   

4. Примеры полнотекстового поиска:
   SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';
   Пример вывода:

   Раскрыть type=sql
                   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@';
   Пример вывода:

   Раскрыть type=sql
                   path
   ------------------------------------
    Top.Science.Astronomy
    Top.Science.Astronomy.Astrophysics
    Top.Science.Astronomy.Cosmology
   (3 rows)
   

5. Образование пути с помощью функций:
   SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
   Пример вывода:

   Раскрыть type=sql
                    ?column?
   ------------------------------------------
    Top.Science.Space.Astronomy
    Top.Science.Space.Astronomy.Astrophysics
    Top.Science.Space.Astronomy.Cosmology
   (3 rows)
   

6. Для упрощения процедуры создать функцию 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';
   Пример вывода:

   Раскрыть type=sql
                   ins_label
   ------------------------------------------
    Top.Science.Space.Astronomy
    Top.Science.Space.Astronomy.Astrophysics
    Top.Science.Space.Astronomy.Cosmology
   (3 rows)
   

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