doc/src/sgml/ltree.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="ltree">
   4  <title>ltree</title>
   5
   6  <indexterm zone="ltree">
   7   <primary>ltree</primary>
   8  </indexterm>
   9
  10  <para>
  11   This module implements a data type <type>ltree</> for representing
  12   labels of data stored in a hierarchical tree-like structure.
  13   Extensive facilities for searching through label trees are provided.
  14  </para>
  15
  16  <sect2>
  17   <title>Definitions</title>
  18
  19   <para>
  20    A <firstterm>label</firstterm> is a sequence of alphanumeric characters
  21    and underscores (for example, in C locale the characters
  22    <literal>A-Za-z0-9_</> are allowed).  Labels must be less than 256 bytes
  23    long.
  24   </para>
  25
  26   <para>
  27    Examples: <literal>42</>, <literal>Personal_Services</>
  28   </para>
  29
  30   <para>
  31    A <firstterm>label path</firstterm> is a sequence of zero or more
  32    labels separated by dots, for example <literal>L1.L2.L3</>, representing
  33    a path from the root of a hierarchical tree to a particular node.  The
  34    length of a label path must be less than 65Kb, but keeping it under 2Kb is
  35    preferable.  In practice this is not a major limitation; for example,
  36    the longest label path in the DMOZ catalogue (<ulink
  37    url="http://www.dmoz.org"></ulink>) is about 240 bytes.
  38   </para>
  39
  40   <para>
  41    Example: <literal>Top.Countries.Europe.Russia</literal>
  42   </para>
  43
  44   <para>
  45    The <filename>ltree</> module provides several datatypes:
  46   </para>
  47
  48   <itemizedlist>
  49    <listitem>
  50     <para>
  51      <type>ltree</type> stores a label path.
  52     </para>
  53    </listitem>
  54
  55    <listitem>
  56     <para>
  57      <type>lquery</type> represents a regular-expression-like pattern
  58      for matching <type>ltree</> values.  A simple word matches that
  59      label within a path.  A star symbol (<literal>*</>) matches zero
  60      or more labels.  For example:
  61      <programlisting>
  62 foo         <lineannotation>Match the exact label path <literal>foo</></lineannotation>
  63 *.foo.*     <lineannotation>Match any label path containing the label <literal>foo</></lineannotation>
  64 *.foo       <lineannotation>Match any label path whose last label is <literal>foo</></lineannotation>
  65      </programlisting>
  66     </para>
  67
  68     <para>
  69      Star symbols can also be quantified to restrict how many labels
  70      they can match:
  71      <programlisting>
  72 *{<replaceable>n</>}        <lineannotation>Match exactly <replaceable>n</> labels</lineannotation>
  73 *{<replaceable>n</>,}       <lineannotation>Match at least <replaceable>n</> labels</lineannotation>
  74 *{<replaceable>n</>,<replaceable>m</>}      <lineannotation>Match at least <replaceable>n</> but not more than <replaceable>m</> labels</lineannotation>
  75 *{,<replaceable>m</>}       <lineannotation>Match at most <replaceable>m</> labels &mdash; same as </lineannotation> *{0,<replaceable>m</>}
  76      </programlisting>
  77     </para>
  78
  79     <para>
  80      There are several modifiers that can be put at the end of a non-star
  81      label in <type>lquery</> to make it match more than just the exact match:
  82      <programlisting>
  83 @           <lineannotation>Match case-insensitively, for example <literal>a@</> matches <literal>A</></lineannotation>
  84 *           <lineannotation>Match any label with this prefix, for example <literal>foo*</> matches <literal>foobar</></lineannotation>
  85 %           <lineannotation>Match initial underscore-separated words</lineannotation>
  86      </programlisting>
  87      The behavior of <literal>%</> is a bit complicated.  It tries to match
  88      words rather than the entire label.  For example
  89      <literal>foo_bar%</> matches <literal>foo_bar_baz</> but not
  90      <literal>foo_barbaz</>.  If combined with <literal>*</>, prefix
  91      matching applies to each word separately, for example
  92      <literal>foo_bar%*</> matches <literal>foo1_bar2_baz</> but
  93      not <literal>foo1_br2_baz</>.
  94     </para>
  95
  96     <para>
  97      Also, you can write several possibly-modified labels separated with
  98      <literal>|</> (OR) to match any of those labels, and you can put
  99      <literal>!</> (NOT) at the start to match any label that doesn't
 100      match any of the alternatives.
 101     </para>
 102
 103     <para>
 104      Here's an annotated example of <type>lquery</type>:
 105     <programlisting>
 106      Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain
 107      a.  b.     c.      d.               e.
 108     </programlisting>
 109      This query will match any label path that:
 110     </para>
 111     <orderedlist numeration='loweralpha'>
 112      <listitem>
 113       <para>
 114        begins with the label <literal>Top</literal>
 115       </para>
 116      </listitem>
 117      <listitem>
 118       <para>
 119        and next has zero to two labels before
 120       </para>
 121      </listitem>
 122      <listitem>
 123       <para>
 124        a label beginning with the case-insensitive prefix <literal>sport</literal>
 125       </para>
 126      </listitem>
 127      <listitem>
 128       <para>
 129        then a label not matching <literal>football</literal> nor
 130        <literal>tennis</literal>
 131       </para>
 132      </listitem>
 133      <listitem>
 134       <para>
 135        and then ends with a label beginning with <literal>Russ</literal> or
 136        exactly matching <literal>Spain</literal>.
 137       </para>
 138      </listitem>
 139     </orderedlist>
 140    </listitem>
 141
 142    <listitem>
 143     <para><type>ltxtquery</type> represents a full-text-search-like
 144     pattern for matching <type>ltree</> values.  An
 145     <type>ltxtquery</type> value contains words, possibly with the
 146     modifiers <literal>@</>, <literal>*</>, <literal>%</> at the end;
 147     the modifiers have the same meanings as in <type>lquery</>.
 148     Words can be combined with <literal>&amp;</> (AND),
 149     <literal>|</> (OR), <literal>!</> (NOT), and parentheses.
 150     The key difference from
 151     <type>lquery</> is that <type>ltxtquery</type> matches words without
 152     regard to their position in the label path.
 153     </para>
 154
 155     <para>
 156      Here's an example <type>ltxtquery</type>:
 157     <programlisting>
 158      Europe &amp; Russia*@ &amp; !Transportation
 159     </programlisting>
 160      This will match paths that contain the label <literal>Europe</literal> and
 161      any label beginning with <literal>Russia</literal> (case-insensitive),
 162      but not paths containing the label <literal>Transportation</literal>.
 163      The location of these words within the path is not important.
 164      Also, when <literal>%</> is used, the word can be matched to any
 165      underscore-separated word within a label, regardless of position.
 166     </para>
 167    </listitem>
 168
 169   </itemizedlist>
 170
 171   <para>
 172    Note: <type>ltxtquery</> allows whitespace between symbols, but
 173    <type>ltree</> and <type>lquery</> do not.
 174   </para>
 175  </sect2>
 176
 177  <sect2>
 178   <title>Operators and Functions</title>
 179
 180   <para>
 181    Type <type>ltree</> has the usual comparison operators
 182    <literal>=</>, <literal>&lt;&gt;</literal>,
 183    <literal>&lt;</>, <literal>&gt;</>, <literal>&lt;=</>, <literal>&gt;=</>.
 184    Comparison sorts in the order of a tree traversal, with the children
 185    of a node sorted by label text.  In addition, there are the following
 186    specialized operators:
 187   </para>
 188
 189   <table id="ltree-op-table">
 190    <title><type>ltree</> Operators</title>
 191
 192    <tgroup cols="3">
 193     <thead>
 194      <row>
 195       <entry>Operator</entry>
 196       <entry>Returns</entry>
 197       <entry>Description</entry>
 198      </row>
 199     </thead>
 200
 201     <tbody>
 202      <row>
 203       <entry><type>ltree</> <literal>@&gt;</> <type>ltree</></entry>
 204       <entry><type>boolean</type></entry>
 205       <entry>is left argument an ancestor of right (or equal)?</entry>
 206      </row>
 207
 208      <row>
 209       <entry><type>ltree</> <literal>&lt;@</> <type>ltree</></entry>
 210       <entry><type>boolean</type></entry>
 211       <entry>is left argument a descendant of right (or equal)?</entry>
 212      </row>
 213
 214      <row>
 215       <entry><type>ltree</> <literal>~</> <type>lquery</></entry>
 216       <entry><type>boolean</type></entry>
 217       <entry>does <type>ltree</> match <type>lquery</>?</entry>
 218      </row>
 219
 220      <row>
 221       <entry><type>lquery</> <literal>~</> <type>ltree</></entry>
 222       <entry><type>boolean</type></entry>
 223       <entry>does <type>ltree</> match <type>lquery</>?</entry>
 224      </row>
 225
 226      <row>
 227       <entry><type>ltree</> <literal>?</> <type>lquery[]</></entry>
 228       <entry><type>boolean</type></entry>
 229       <entry>does <type>ltree</> match any <type>lquery</> in array?</entry>
 230      </row>
 231
 232      <row>
 233       <entry><type>lquery[]</> <literal>?</> <type>ltree</></entry>
 234       <entry><type>boolean</type></entry>
 235       <entry>does <type>ltree</> match any <type>lquery</> in array?</entry>
 236      </row>
 237
 238      <row>
 239       <entry><type>ltree</> <literal>@</> <type>ltxtquery</></entry>
 240       <entry><type>boolean</type></entry>
 241       <entry>does <type>ltree</> match <type>ltxtquery</>?</entry>
 242      </row>
 243
 244      <row>
 245       <entry><type>ltxtquery</> <literal>@</> <type>ltree</></entry>
 246       <entry><type>boolean</type></entry>
 247       <entry>does <type>ltree</> match <type>ltxtquery</>?</entry>
 248      </row>
 249
 250      <row>
 251       <entry><type>ltree</> <literal>||</> <type>ltree</></entry>
 252       <entry><type>ltree</type></entry>
 253       <entry>concatenate <type>ltree</> paths</entry>
 254      </row>
 255
 256      <row>
 257       <entry><type>ltree</> <literal>||</> <type>text</></entry>
 258       <entry><type>ltree</type></entry>
 259       <entry>convert text to <type>ltree</> and concatenate</entry>
 260      </row>
 261
 262      <row>
 263       <entry><type>text</> <literal>||</> <type>ltree</></entry>
 264       <entry><type>ltree</type></entry>
 265       <entry>convert text to <type>ltree</> and concatenate</entry>
 266      </row>
 267
 268      <row>
 269       <entry><type>ltree[]</> <literal>@&gt;</> <type>ltree</></entry>
 270       <entry><type>boolean</type></entry>
 271       <entry>does array contain an ancestor of <type>ltree</>?</entry>
 272      </row>
 273
 274      <row>
 275       <entry><type>ltree</> <literal>&lt;@</> <type>ltree[]</></entry>
 276       <entry><type>boolean</type></entry>
 277       <entry>does array contain an ancestor of <type>ltree</>?</entry>
 278      </row>
 279
 280      <row>
 281       <entry><type>ltree[]</> <literal>&lt;@</> <type>ltree</></entry>
 282       <entry><type>boolean</type></entry>
 283       <entry>does array contain a descendant of <type>ltree</>?</entry>
 284      </row>
 285
 286      <row>
 287       <entry><type>ltree</> <literal>@&gt;</> <type>ltree[]</></entry>
 288       <entry><type>boolean</type></entry>
 289       <entry>does array contain a descendant of <type>ltree</>?</entry>
 290      </row>
 291
 292      <row>
 293       <entry><type>ltree[]</> <literal>~</> <type>lquery</></entry>
 294       <entry><type>boolean</type></entry>
 295       <entry>does array contain any path matching <type>lquery</>?</entry>
 296      </row>
 297
 298      <row>
 299       <entry><type>lquery</> <literal>~</> <type>ltree[]</></entry>
 300       <entry><type>boolean</type></entry>
 301       <entry>does array contain any path matching <type>lquery</>?</entry>
 302      </row>
 303
 304      <row>
 305       <entry><type>ltree[]</> <literal>?</> <type>lquery[]</></entry>
 306       <entry><type>boolean</type></entry>
 307       <entry>does <type>ltree</> array contain any path matching any <type>lquery</>?</entry>
 308      </row>
 309
 310      <row>
 311       <entry><type>lquery[]</> <literal>?</> <type>ltree[]</></entry>
 312       <entry><type>boolean</type></entry>
 313       <entry>does <type>ltree</> array contain any path matching any <type>lquery</>?</entry>
 314      </row>
 315
 316      <row>
 317       <entry><type>ltree[]</> <literal>@</> <type>ltxtquery</></entry>
 318       <entry><type>boolean</type></entry>
 319       <entry>does array contain any path matching <type>ltxtquery</>?</entry>
 320      </row>
 321
 322      <row>
 323       <entry><type>ltxtquery</> <literal>@</> <type>ltree[]</></entry>
 324       <entry><type>boolean</type></entry>
 325       <entry>does array contain any path matching <type>ltxtquery</>?</entry>
 326      </row>
 327
 328      <row>
 329       <entry><type>ltree[]</> <literal>?@&gt;</> <type>ltree</></entry>
 330       <entry><type>ltree</type></entry>
 331       <entry>first array entry that is an ancestor of <type>ltree</>; NULL if none</entry>
 332      </row>
 333
 334      <row>
 335       <entry><type>ltree[]</> <literal>?&lt;@</> <type>ltree</></entry>
 336       <entry><type>ltree</type></entry>
 337       <entry>first array entry that is a descendant of <type>ltree</>; NULL if none</entry>
 338      </row>
 339
 340      <row>
 341       <entry><type>ltree[]</> <literal>?~</> <type>lquery</></entry>
 342       <entry><type>ltree</type></entry>
 343       <entry>first array entry that matches <type>lquery</>; NULL if none</entry>
 344      </row>
 345
 346      <row>
 347       <entry><type>ltree[]</> <literal>?@</> <type>ltxtquery</></entry>
 348       <entry><type>ltree</type></entry>
 349       <entry>first array entry that matches <type>ltxtquery</>; NULL if none</entry>
 350      </row>
 351
 352     </tbody>
 353    </tgroup>
 354   </table>
 355
 356   <para>
 357    The operators <literal>&lt;@</literal>, <literal>@&gt;</literal>,
 358    <literal>@</literal> and <literal>~</literal> have analogues
 359    <literal>^&lt;@</>, <literal>^@&gt;</>, <literal>^@</>,
 360    <literal>^~</literal>, which are the same except they do not use
 361    indexes.  These are useful only for testing purposes.
 362   </para>
 363
 364   <para>
 365    The following functions are available:
 366   </para>
 367
 368   <table id="ltree-func-table">
 369    <title><type>ltree</> Functions</title>
 370
 371    <tgroup cols="5">
 372     <thead>
 373      <row>
 374       <entry>Function</entry>
 375       <entry>Return Type</entry>
 376       <entry>Description</entry>
 377       <entry>Example</entry>
 378       <entry>Result</entry>
 379      </row>
 380     </thead>
 381
 382     <tbody>
 383      <row>
 384       <entry><function>subltree(ltree, int start, int end)</function></entry>
 385       <entry><type>ltree</type></entry>
 386       <entry>subpath of <type>ltree</> from position <parameter>start</> to
 387        position <parameter>end</>-1 (counting from 0)</entry>
 388       <entry><literal>subltree('Top.Child1.Child2',1,2)</literal></entry>
 389       <entry><literal>Child1</literal></entry>
 390      </row>
 391
 392      <row>
 393       <entry><function>subpath(ltree, int offset, int len)</function></entry>
 394       <entry><type>ltree</type></entry>
 395       <entry>subpath of <type>ltree</> starting at position
 396        <parameter>offset</>, length <parameter>len</>.
 397        If <parameter>offset</> is negative, subpath starts that far from the
 398        end of the path.  If <parameter>len</> is negative, leaves that many
 399        labels off the end of the path.</entry>
 400       <entry><literal>subpath('Top.Child1.Child2',0,2)</literal></entry>
 401       <entry><literal>Top.Child1</literal></entry>
 402      </row>
 403
 404      <row>
 405       <entry><function>subpath(ltree, int offset)</function></entry>
 406       <entry><type>ltree</type></entry>
 407       <entry>subpath of <type>ltree</> starting at position
 408        <parameter>offset</>, extending to end of path.
 409        If <parameter>offset</> is negative, subpath starts that far from the
 410        end of the path.</entry>
 411       <entry><literal>subpath('Top.Child1.Child2',1)</literal></entry>
 412       <entry><literal>Child1.Child2</literal></entry>
 413      </row>
 414
 415      <row>
 416       <entry><function>nlevel(ltree)</function></entry>
 417       <entry><type>integer</type></entry>
 418       <entry>number of labels in path</entry>
 419       <entry><literal>nlevel('Top.Child1.Child2')</literal></entry>
 420       <entry><literal>3</literal></entry>
 421      </row>
 422
 423      <row>
 424       <entry><function>index(ltree a, ltree b)</function></entry>
 425       <entry><type>integer</type></entry>
 426       <entry>position of first occurrence of <parameter>b</> in
 427        <parameter>a</>; -1 if not found</entry>
 428       <entry><literal>index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')</literal></entry>
 429       <entry><literal>6</literal></entry>
 430      </row>
 431
 432      <row>
 433       <entry><function>index(ltree a, ltree b, int offset)</function></entry>
 434       <entry><type>integer</type></entry>
 435       <entry>position of first occurrence of <parameter>b</> in
 436        <parameter>a</>, searching starting at <parameter>offset</>;
 437        negative <parameter>offset</> means start <parameter>-offset</>
 438        labels from the end of the path</entry>
 439       <entry><literal>index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)</literal></entry>
 440       <entry><literal>9</literal></entry>
 441      </row>
 442
 443      <row>
 444       <entry><function>text2ltree(text)</function></entry>
 445       <entry><type>ltree</type></entry>
 446       <entry>cast <type>text</> to <type>ltree</></entry>
 447       <entry><literal></literal></entry>
 448       <entry><literal></literal></entry>
 449      </row>
 450
 451      <row>
 452       <entry><function>ltree2text(ltree)</function></entry>
 453       <entry><type>text</type></entry>
 454       <entry>cast <type>ltree</> to <type>text</></entry>
 455       <entry><literal></literal></entry>
 456       <entry><literal></literal></entry>
 457      </row>
 458
 459      <row>
 460       <entry><function>lca(ltree, ltree, ...)</function></entry>
 461       <entry><type>ltree</type></entry>
 462       <entry>lowest common ancestor, i.e., longest common prefix of paths
 463        (up to 8 arguments supported)</entry>
 464       <entry><literal>lca('1.2.2.3','1.2.3.4.5.6')</literal></entry>
 465       <entry><literal>1.2</literal></entry>
 466      </row>
 467
 468      <row>
 469       <entry><function>lca(ltree[])</function></entry>
 470       <entry><type>ltree</type></entry>
 471       <entry>lowest common ancestor, i.e., longest common prefix of paths</entry>
 472       <entry><literal>lca(array['1.2.2.3'::ltree,'1.2.3'])</literal></entry>
 473       <entry><literal>1.2</literal></entry>
 474      </row>
 475
 476     </tbody>
 477    </tgroup>
 478   </table>
 479  </sect2>
 480
 481  <sect2>
 482   <title>Indexes</title>
 483   <para>
 484    <filename>ltree</> supports several types of indexes that can speed
 485    up the indicated operators:
 486   </para>
 487
 488   <itemizedlist>
 489    <listitem>
 490     <para>
 491      B-tree index over <type>ltree</>:
 492      <literal>&lt;</>, <literal>&lt;=</>, <literal>=</>,
 493      <literal>&gt;=</>, <literal>&gt;</literal>
 494     </para>
 495    </listitem>
 496    <listitem>
 497     <para>
 498      GiST index over <type>ltree</>:
 499      <literal>&lt;</>, <literal>&lt;=</>, <literal>=</>,
 500      <literal>&gt;=</>, <literal>&gt;</>,
 501      <literal>@&gt;</>, <literal>&lt;@</>,
 502      <literal>@</>, <literal>~</>, <literal>?</literal>
 503     </para>
 504     <para>
 505      Example of creating such an index:
 506     </para>
 507     <programlisting>
 508      CREATE INDEX path_gist_idx ON test USING GIST (path);
 509     </programlisting>
 510    </listitem>
 511    <listitem>
 512     <para>
 513      GiST index over <type>ltree[]</>:
 514      <literal>ltree[] &lt;@ ltree</>, <literal>ltree @&gt; ltree[]</>,
 515      <literal>@</>, <literal>~</>, <literal>?</literal>
 516     </para>
 517     <para>
 518      Example of creating such an index:
 519     </para>
 520     <programlisting>
 521      CREATE INDEX path_gist_idx ON test USING GIST (array_path);
 522     </programlisting>
 523     <para>
 524      Note: This index type is lossy.
 525     </para>
 526    </listitem>
 527   </itemizedlist>
 528  </sect2>
 529
 530  <sect2>
 531   <title>Example</title>
 532
 533   <para>
 534    This example uses the following data (also available in file
 535    <filename>contrib/ltree/ltreetest.sql</> in the source distribution):
 536   </para>
 537
 538   <programlisting>
 539 CREATE TABLE test (path ltree);
 540 INSERT INTO test VALUES ('Top');
 541 INSERT INTO test VALUES ('Top.Science');
 542 INSERT INTO test VALUES ('Top.Science.Astronomy');
 543 INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
 544 INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
 545 INSERT INTO test VALUES ('Top.Hobbies');
 546 INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
 547 INSERT INTO test VALUES ('Top.Collections');
 548 INSERT INTO test VALUES ('Top.Collections.Pictures');
 549 INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
 550 INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
 551 INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
 552 INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
 553 CREATE INDEX path_gist_idx ON test USING gist(path);
 554 CREATE INDEX path_idx ON test USING btree(path);
 555   </programlisting>
 556
 557   <para>
 558    Now, we have a table <structname>test</> populated with data describing
 559    the hierarchy shown below:
 560   </para>
 561
 562   <programlisting>
 563                             Top
 564                          /   |  \
 565                  Science Hobbies Collections
 566                      /       |              \
 567             Astronomy   Amateurs_Astronomy Pictures
 568                /  \                            |
 569     Astrophysics  Cosmology                Astronomy
 570                                             /  |    \
 571                                      Galaxies Stars Astronauts
 572   </programlisting>
 573
 574   <para>
 575    We can do inheritance:
 576   </para>
 577
 578   <programlisting>
 579 ltreetest=# select path from test where path &lt;@ 'Top.Science';
 580                 path
 581 ------------------------------------
 582  Top.Science
 583  Top.Science.Astronomy
 584  Top.Science.Astronomy.Astrophysics
 585  Top.Science.Astronomy.Cosmology
 586 (4 rows)
 587   </programlisting>
 588
 589   <para>
 590    Here are some examples of path matching:
 591   </para>
 592
 593   <programlisting>
 594 ltreetest=# select path from test where path ~ '*.Astronomy.*';
 595                      path
 596 -----------------------------------------------
 597  Top.Science.Astronomy
 598  Top.Science.Astronomy.Astrophysics
 599  Top.Science.Astronomy.Cosmology
 600  Top.Collections.Pictures.Astronomy
 601  Top.Collections.Pictures.Astronomy.Stars
 602  Top.Collections.Pictures.Astronomy.Galaxies
 603  Top.Collections.Pictures.Astronomy.Astronauts
 604 (7 rows)
 605
 606 ltreetest=# select path from test where path ~ '*.!pictures@.*.Astronomy.*';
 607                 path
 608 ------------------------------------
 609  Top.Science.Astronomy
 610  Top.Science.Astronomy.Astrophysics
 611  Top.Science.Astronomy.Cosmology
 612 (3 rows)
 613   </programlisting>
 614
 615   <para>
 616    Here are some examples of full text search:
 617   </para>
 618   <programlisting>
 619 ltreetest=# select path from test where path @ 'Astro*% &amp; !pictures@';
 620                 path
 621 ------------------------------------
 622  Top.Science.Astronomy
 623  Top.Science.Astronomy.Astrophysics
 624  Top.Science.Astronomy.Cosmology
 625  Top.Hobbies.Amateurs_Astronomy
 626 (4 rows)
 627
 628 ltreetest=# select path from test where path @ 'Astro* &amp; !pictures@';
 629                 path
 630 ------------------------------------
 631  Top.Science.Astronomy
 632  Top.Science.Astronomy.Astrophysics
 633  Top.Science.Astronomy.Cosmology
 634 (3 rows)
 635   </programlisting>
 636
 637   <para>
 638    Path construction using functions:
 639   </para>
 640   <programlisting>
 641 ltreetest=# select subpath(path,0,2)||'Space'||subpath(path,2) from test where path &lt;@ 'Top.Science.Astronomy';
 642                  ?column?
 643 ------------------------------------------
 644  Top.Science.Space.Astronomy
 645  Top.Science.Space.Astronomy.Astrophysics
 646  Top.Science.Space.Astronomy.Cosmology
 647 (3 rows)
 648   </programlisting>
 649
 650   <para>
 651    We could simplify this by creating a SQL function that inserts a label
 652    at a specified position in a path:
 653   </para>
 654   <programlisting>
 655 CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
 656 AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
 657 LANGUAGE SQL IMMUTABLE;
 658
 659 ltreetest=# select ins_label(path,2,'Space') from test where path &lt;@ 'Top.Science.Astronomy';
 660                 ins_label
 661 ------------------------------------------
 662  Top.Science.Space.Astronomy
 663  Top.Science.Space.Astronomy.Astrophysics
 664  Top.Science.Space.Astronomy.Cosmology
 665 (3 rows)
 666   </programlisting>
 667  </sect2>
 668
 669  <sect2>
 670   <title>Authors</title>
 671
 672   <para>
 673    All work was done by Teodor Sigaev (<email>teodor@stack.net</email>) and
 674    Oleg Bartunov (<email>oleg@sai.msu.su</email>). See
 675    <ulink url="http://www.sai.msu.su/~megera/postgres/gist"></ulink> for
 676    additional information. Authors would like to thank Eugeny Rodichev for
 677    helpful discussions. Comments and bug reports are welcome.
 678   </para>
 679  </sect2>
 680
 681 </sect1>