Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Bevaka 1
Stjärnmärk 0
Wiki 捐赠
gooderp18绿色标准版
Du kan inte välja fler än 25 ämnen Ämnen måste starta med en bokstav eller siffra, kan innehålla bindestreck ('-') och vara max 35 tecken långa.
Gren: master
Grenar Taggar
${ item.name }
Skapa branchen ${ searchTerm }
från 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/ltree.html

296 lines
30KB
Rå Permalänk Blame Historik 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.21. ltree</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="lo.html" title="F.20. lo" /><link rel="next" href="pageinspect.html" title="F.22. pageinspect" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.21. ltree</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="lo.html" title="F.20. lo">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pageinspect.html" title="F.22. pageinspect">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="LTREE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.21. ltree</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.4">F.21.1. Definitions</a></span></dt><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.5">F.21.2. Operators and Functions</a></span></dt><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.6">F.21.3. Indexes</a></span></dt><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.7">F.21.4. Example</a></span></dt><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.8">F.21.5. Transforms</a></span></dt><dt><span class="sect2"><a href="ltree.html#id-1.11.7.30.9">F.21.6. Authors</a></span></dt></dl></div><a id="id-1.11.7.30.2" class="indexterm"></a><p>

  3.   This module implements a data type <code class="type">ltree</code> for representing

  4.   labels of data stored in a hierarchical tree-like structure.

  5.   Extensive facilities for searching through label trees are provided.

  6.  </p><div class="sect2" id="id-1.11.7.30.4"><div class="titlepage"><div><div><h3 class="title">F.21.1. Definitions</h3></div></div></div><p>

  7.    A <em class="firstterm">label</em> is a sequence of alphanumeric characters

  8.    and underscores (for example, in C locale the characters

  9.    <code class="literal">A-Za-z0-9_</code> are allowed).

  10.    Labels must be less than 256 characters long.

  11.   </p><p>

  12.    Examples: <code class="literal">42</code>, <code class="literal">Personal_Services</code>

  13.   </p><p>

  14.    A <em class="firstterm">label path</em> is a sequence of zero or more

  15.    labels separated by dots, for example <code class="literal">L1.L2.L3</code>, representing

  16.    a path from the root of a hierarchical tree to a particular node.  The

  17.    length of a label path cannot exceed 65535 labels.

  18.   </p><p>

  19.    Example: <code class="literal">Top.Countries.Europe.Russia</code>

  20.   </p><p>

  21.    The <code class="filename">ltree</code> module provides several data types:

  22.   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  23.      <code class="type">ltree</code> stores a label path.

  24.     </p></li><li class="listitem"><p>

  25.      <code class="type">lquery</code> represents a regular-expression-like pattern

  26.      for matching <code class="type">ltree</code> values.  A simple word matches that

  27.      label within a path.  A star symbol (<code class="literal">*</code>) matches zero

  28.      or more labels.  For example:

  29. </p><pre class="synopsis">

  30. foo         <em class="lineannotation"><span class="lineannotation">Match the exact label path <code class="literal">foo</code></span></em>

  31. *.foo.*     <em class="lineannotation"><span class="lineannotation">Match any label path containing the label <code class="literal">foo</code></span></em>

  32. *.foo       <em class="lineannotation"><span class="lineannotation">Match any label path whose last label is <code class="literal">foo</code></span></em>

  33. </pre><p>

  34.     </p><p>

  35.      Star symbols can also be quantified to restrict how many labels

  36.      they can match:

  37. </p><pre class="synopsis">

  38. *{<em class="replaceable"><code>n</code></em>}        <em class="lineannotation"><span class="lineannotation">Match exactly <em class="replaceable"><code>n</code></em> labels</span></em>

  39. *{<em class="replaceable"><code>n</code></em>,}       <em class="lineannotation"><span class="lineannotation">Match at least <em class="replaceable"><code>n</code></em> labels</span></em>

  40. *{<em class="replaceable"><code>n</code></em>,<em class="replaceable"><code>m</code></em>}      <em class="lineannotation"><span class="lineannotation">Match at least <em class="replaceable"><code>n</code></em> but not more than <em class="replaceable"><code>m</code></em> labels</span></em>

  41. *{,<em class="replaceable"><code>m</code></em>}       <em class="lineannotation"><span class="lineannotation">Match at most <em class="replaceable"><code>m</code></em> labels — same as </span></em> *{0,<em class="replaceable"><code>m</code></em>}

  42. </pre><p>

  43.     </p><p>

  44.      There are several modifiers that can be put at the end of a non-star

  45.      label in <code class="type">lquery</code> to make it match more than just the exact match:

  46. </p><pre class="synopsis">

  47. @           <em class="lineannotation"><span class="lineannotation">Match case-insensitively, for example <code class="literal">a@</code> matches <code class="literal">A</code></span></em>

  48. *           <em class="lineannotation"><span class="lineannotation">Match any label with this prefix, for example <code class="literal">foo*</code> matches <code class="literal">foobar</code></span></em>

  49. %           <em class="lineannotation"><span class="lineannotation">Match initial underscore-separated words</span></em>

  50. </pre><p>

  51.      The behavior of <code class="literal">%</code> is a bit complicated.  It tries to match

  52.      words rather than the entire label.  For example

  53.      <code class="literal">foo_bar%</code> matches <code class="literal">foo_bar_baz</code> but not

  54.      <code class="literal">foo_barbaz</code>.  If combined with <code class="literal">*</code>, prefix

  55.      matching applies to each word separately, for example

  56.      <code class="literal">foo_bar%*</code> matches <code class="literal">foo1_bar2_baz</code> but

  57.      not <code class="literal">foo1_br2_baz</code>.

  58.     </p><p>

  59.      Also, you can write several possibly-modified labels separated with

  60.      <code class="literal">|</code> (OR) to match any of those labels, and you can put

  61.      <code class="literal">!</code> (NOT) at the start to match any label that doesn't

  62.      match any of the alternatives.

  63.     </p><p>

  64.      Here's an annotated example of <code class="type">lquery</code>:

  65. </p><pre class="programlisting">

  66. Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain

  67. a.  b.     c.      d.               e.

  68. </pre><p>

  69.      This query will match any label path that:

  70.     </p><div class="orderedlist"><ol class="orderedlist" type="a"><li class="listitem"><p>

  71.        begins with the label <code class="literal">Top</code>

  72.       </p></li><li class="listitem"><p>

  73.        and next has zero to two labels before

  74.       </p></li><li class="listitem"><p>

  75.        a label beginning with the case-insensitive prefix <code class="literal">sport</code>

  76.       </p></li><li class="listitem"><p>

  77.        then a label not matching <code class="literal">football</code> nor

  78.        <code class="literal">tennis</code>

  79.       </p></li><li class="listitem"><p>

  80.        and then ends with a label beginning with <code class="literal">Russ</code> or

  81.        exactly matching <code class="literal">Spain</code>.

  82.       </p></li></ol></div></li><li class="listitem"><p><code class="type">ltxtquery</code> represents a full-text-search-like

  83.     pattern for matching <code class="type">ltree</code> values.  An

  84.     <code class="type">ltxtquery</code> value contains words, possibly with the

  85.     modifiers <code class="literal">@</code>, <code class="literal">*</code>, <code class="literal">%</code> at the end;

  86.     the modifiers have the same meanings as in <code class="type">lquery</code>.

  87.     Words can be combined with <code class="literal">&amp;</code> (AND),

  88.     <code class="literal">|</code> (OR), <code class="literal">!</code> (NOT), and parentheses.

  89.     The key difference from

  90.     <code class="type">lquery</code> is that <code class="type">ltxtquery</code> matches words without

  91.     regard to their position in the label path.

  92.     </p><p>

  93.      Here's an example <code class="type">ltxtquery</code>:

  94. </p><pre class="programlisting">

  95. Europe &amp; Russia*@ &amp; !Transportation

  96. </pre><p>

  97.      This will match paths that contain the label <code class="literal">Europe</code> and

  98.      any label beginning with <code class="literal">Russia</code> (case-insensitive),

  99.      but not paths containing the label <code class="literal">Transportation</code>.

  100.      The location of these words within the path is not important.

  101.      Also, when <code class="literal">%</code> is used, the word can be matched to any

  102.      underscore-separated word within a label, regardless of position.

  103.     </p></li></ul></div><p>

  104.    Note: <code class="type">ltxtquery</code> allows whitespace between symbols, but

  105.    <code class="type">ltree</code> and <code class="type">lquery</code> do not.

  106.   </p></div><div class="sect2" id="id-1.11.7.30.5"><div class="titlepage"><div><div><h3 class="title">F.21.2. Operators and Functions</h3></div></div></div><p>

  107.    Type <code class="type">ltree</code> has the usual comparison operators

  108.    <code class="literal">=</code>, <code class="literal">&lt;&gt;</code>,

  109.    <code class="literal">&lt;</code>, <code class="literal">&gt;</code>, <code class="literal">&lt;=</code>, <code class="literal">&gt;=</code>.

  110.    Comparison sorts in the order of a tree traversal, with the children

  111.    of a node sorted by label text.  In addition, the specialized

  112.    operators shown in <a class="xref" href="ltree.html#LTREE-OP-TABLE" title="Table F.13. ltree Operators">Table F.13</a> are available.

  113.   </p><div class="table" id="LTREE-OP-TABLE"><p class="title"><strong>Table F.13. <code class="type">ltree</code> Operators</strong></p><div class="table-contents"><table class="table" summary="ltree Operators" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Returns</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">ltree</code> <code class="literal">@&gt;</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>is left argument an ancestor of right (or equal)?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">&lt;@</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>is left argument a descendant of right (or equal)?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">~</code> <code class="type">lquery</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match <code class="type">lquery</code>?</td></tr><tr><td><code class="type">lquery</code> <code class="literal">~</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match <code class="type">lquery</code>?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">?</code> <code class="type">lquery[]</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match any <code class="type">lquery</code> in array?</td></tr><tr><td><code class="type">lquery[]</code> <code class="literal">?</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match any <code class="type">lquery</code> in array?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">@</code> <code class="type">ltxtquery</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match <code class="type">ltxtquery</code>?</td></tr><tr><td><code class="type">ltxtquery</code> <code class="literal">@</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> match <code class="type">ltxtquery</code>?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">||</code> <code class="type">ltree</code></td><td><code class="type">ltree</code></td><td>concatenate <code class="type">ltree</code> paths</td></tr><tr><td><code class="type">ltree</code> <code class="literal">||</code> <code class="type">text</code></td><td><code class="type">ltree</code></td><td>convert text to <code class="type">ltree</code> and concatenate</td></tr><tr><td><code class="type">text</code> <code class="literal">||</code> <code class="type">ltree</code></td><td><code class="type">ltree</code></td><td>convert text to <code class="type">ltree</code> and concatenate</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">@&gt;</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>does array contain an ancestor of <code class="type">ltree</code>?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">&lt;@</code> <code class="type">ltree[]</code></td><td><code class="type">boolean</code></td><td>does array contain an ancestor of <code class="type">ltree</code>?</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">&lt;@</code> <code class="type">ltree</code></td><td><code class="type">boolean</code></td><td>does array contain a descendant of <code class="type">ltree</code>?</td></tr><tr><td><code class="type">ltree</code> <code class="literal">@&gt;</code> <code class="type">ltree[]</code></td><td><code class="type">boolean</code></td><td>does array contain a descendant of <code class="type">ltree</code>?</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">~</code> <code class="type">lquery</code></td><td><code class="type">boolean</code></td><td>does array contain any path matching <code class="type">lquery</code>?</td></tr><tr><td><code class="type">lquery</code> <code class="literal">~</code> <code class="type">ltree[]</code></td><td><code class="type">boolean</code></td><td>does array contain any path matching <code class="type">lquery</code>?</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">?</code> <code class="type">lquery[]</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> array contain any path matching any <code class="type">lquery</code>?</td></tr><tr><td><code class="type">lquery[]</code> <code class="literal">?</code> <code class="type">ltree[]</code></td><td><code class="type">boolean</code></td><td>does <code class="type">ltree</code> array contain any path matching any <code class="type">lquery</code>?</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">@</code> <code class="type">ltxtquery</code></td><td><code class="type">boolean</code></td><td>does array contain any path matching <code class="type">ltxtquery</code>?</td></tr><tr><td><code class="type">ltxtquery</code> <code class="literal">@</code> <code class="type">ltree[]</code></td><td><code class="type">boolean</code></td><td>does array contain any path matching <code class="type">ltxtquery</code>?</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">?@&gt;</code> <code class="type">ltree</code></td><td><code class="type">ltree</code></td><td>first array entry that is an ancestor of <code class="type">ltree</code>; NULL if none</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">?&lt;@</code> <code class="type">ltree</code></td><td><code class="type">ltree</code></td><td>first array entry that is a descendant of <code class="type">ltree</code>; NULL if none</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">?~</code> <code class="type">lquery</code></td><td><code class="type">ltree</code></td><td>first array entry that matches <code class="type">lquery</code>; NULL if none</td></tr><tr><td><code class="type">ltree[]</code> <code class="literal">?@</code> <code class="type">ltxtquery</code></td><td><code class="type">ltree</code></td><td>first array entry that matches <code class="type">ltxtquery</code>; NULL if none</td></tr></tbody></table></div></div><br class="table-break" /><p>

  114.    The operators <code class="literal">&lt;@</code>, <code class="literal">@&gt;</code>,

  115.    <code class="literal">@</code> and <code class="literal">~</code> have analogues

  116.    <code class="literal">^&lt;@</code>, <code class="literal">^@&gt;</code>, <code class="literal">^@</code>,

  117.    <code class="literal">^~</code>, which are the same except they do not use

  118.    indexes.  These are useful only for testing purposes.

  119.   </p><p>

  120.    The available functions are shown in <a class="xref" href="ltree.html#LTREE-FUNC-TABLE" title="Table F.14. ltree Functions">Table F.14</a>.

  121.   </p><div class="table" id="LTREE-FUNC-TABLE"><p class="title"><strong>Table F.14. <code class="type">ltree</code> Functions</strong></p><div class="table-contents"><table class="table" summary="ltree Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td><code class="function">subltree(ltree, int start, int end)</code><a id="id-1.11.7.30.5.6.2.2.1.1.2" class="indexterm"></a></td><td><code class="type">ltree</code></td><td>subpath of <code class="type">ltree</code> from position <em class="parameter"><code>start</code></em> to

  122.        position <em class="parameter"><code>end</code></em>-1 (counting from 0)</td><td><code class="literal">subltree('Top.Child1.Child2',1,2)</code></td><td><code class="literal">Child1</code></td></tr><tr><td><code class="function">subpath(ltree, int offset, int len)</code><a id="id-1.11.7.30.5.6.2.2.2.1.2" class="indexterm"></a></td><td><code class="type">ltree</code></td><td>subpath of <code class="type">ltree</code> starting at position

  123.        <em class="parameter"><code>offset</code></em>, length <em class="parameter"><code>len</code></em>.

  124.        If <em class="parameter"><code>offset</code></em> is negative, subpath starts that far from the

  125.        end of the path.  If <em class="parameter"><code>len</code></em> is negative, leaves that many

  126.        labels off the end of the path.</td><td><code class="literal">subpath('Top.Child1.Child2',0,2)</code></td><td><code class="literal">Top.Child1</code></td></tr><tr><td><code class="function">subpath(ltree, int offset)</code></td><td><code class="type">ltree</code></td><td>subpath of <code class="type">ltree</code> starting at position

  127.        <em class="parameter"><code>offset</code></em>, extending to end of path.

  128.        If <em class="parameter"><code>offset</code></em> is negative, subpath starts that far from the

  129.        end of the path.</td><td><code class="literal">subpath('Top.Child1.Child2',1)</code></td><td><code class="literal">Child1.Child2</code></td></tr><tr><td><code class="function">nlevel(ltree)</code><a id="id-1.11.7.30.5.6.2.2.4.1.2" class="indexterm"></a></td><td><code class="type">integer</code></td><td>number of labels in path</td><td><code class="literal">nlevel('Top.Child1.Child2')</code></td><td><code class="literal">3</code></td></tr><tr><td><code class="function">index(ltree a, ltree b)</code><a id="id-1.11.7.30.5.6.2.2.5.1.2" class="indexterm"></a></td><td><code class="type">integer</code></td><td>position of first occurrence of <em class="parameter"><code>b</code></em> in

  130.        <em class="parameter"><code>a</code></em>; -1 if not found</td><td><code class="literal">index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')</code></td><td><code class="literal">6</code></td></tr><tr><td><code class="function">index(ltree a, ltree b, int offset)</code></td><td><code class="type">integer</code></td><td>position of first occurrence of <em class="parameter"><code>b</code></em> in

  131.        <em class="parameter"><code>a</code></em>, searching starting at <em class="parameter"><code>offset</code></em>;

  132.        negative <em class="parameter"><code>offset</code></em> means start <em class="parameter"><code>-offset</code></em>

  133.        labels from the end of the path</td><td><code class="literal">index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)</code></td><td><code class="literal">9</code></td></tr><tr><td><code class="function">text2ltree(text)</code><a id="id-1.11.7.30.5.6.2.2.7.1.2" class="indexterm"></a></td><td><code class="type">ltree</code></td><td>cast <code class="type">text</code> to <code class="type">ltree</code></td><td><code class="literal"></code></td><td><code class="literal"></code></td></tr><tr><td><code class="function">ltree2text(ltree)</code><a id="id-1.11.7.30.5.6.2.2.8.1.2" class="indexterm"></a></td><td><code class="type">text</code></td><td>cast <code class="type">ltree</code> to <code class="type">text</code></td><td><code class="literal"></code></td><td><code class="literal"></code></td></tr><tr><td><code class="function">lca(ltree, ltree, ...)</code><a id="id-1.11.7.30.5.6.2.2.9.1.2" class="indexterm"></a></td><td><code class="type">ltree</code></td><td>longest common ancestor of paths

  134.        (up to 8 arguments supported)</td><td><code class="literal">lca('1.2.3','1.2.3.4.5.6')</code></td><td><code class="literal">1.2</code></td></tr><tr><td><code class="function">lca(ltree[])</code></td><td><code class="type">ltree</code></td><td>longest common ancestor of paths in array</td><td><code class="literal">lca(array['1.2.3'::ltree,'1.2.3.4'])</code></td><td><code class="literal">1.2</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="id-1.11.7.30.6"><div class="titlepage"><div><div><h3 class="title">F.21.3. Indexes</h3></div></div></div><p>

  135.    <code class="filename">ltree</code> supports several types of indexes that can speed

  136.    up the indicated operators:

  137.   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>

  138.      B-tree index over <code class="type">ltree</code>:

  139.      <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>, <code class="literal">=</code>,

  140.      <code class="literal">&gt;=</code>, <code class="literal">&gt;</code>

  141.     </p></li><li class="listitem"><p>

  142.      GiST index over <code class="type">ltree</code>:

  143.      <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>, <code class="literal">=</code>,

  144.      <code class="literal">&gt;=</code>, <code class="literal">&gt;</code>,

  145.      <code class="literal">@&gt;</code>, <code class="literal">&lt;@</code>,

  146.      <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code>

  147.     </p><p>

  148.      Example of creating such an index:

  149.     </p><pre class="programlisting">

  150. CREATE INDEX path_gist_idx ON test USING GIST (path);

  151. </pre></li><li class="listitem"><p>

  152.      GiST index over <code class="type">ltree[]</code>:

  153.      <code class="literal">ltree[] &lt;@ ltree</code>, <code class="literal">ltree @&gt; ltree[]</code>,

  154.      <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code>

  155.     </p><p>

  156.      Example of creating such an index:

  157.     </p><pre class="programlisting">

  158. CREATE INDEX path_gist_idx ON test USING GIST (array_path);

  159. </pre><p>

  160.      Note: This index type is lossy.

  161.     </p></li></ul></div></div><div class="sect2" id="id-1.11.7.30.7"><div class="titlepage"><div><div><h3 class="title">F.21.4. Example</h3></div></div></div><p>

  162.    This example uses the following data (also available in file

  163.    <code class="filename">contrib/ltree/ltreetest.sql</code> in the source distribution):

  164.   </p><pre class="programlisting">

  165. CREATE TABLE test (path ltree);

  166. INSERT INTO test VALUES ('Top');

  167. INSERT INTO test VALUES ('Top.Science');

  168. INSERT INTO test VALUES ('Top.Science.Astronomy');

  169. INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');

  170. INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');

  171. INSERT INTO test VALUES ('Top.Hobbies');

  172. INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');

  173. INSERT INTO test VALUES ('Top.Collections');

  174. INSERT INTO test VALUES ('Top.Collections.Pictures');

  175. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');

  176. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');

  177. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');

  178. INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

  179. CREATE INDEX path_gist_idx ON test USING GIST (path);

  180. CREATE INDEX path_idx ON test USING BTREE (path);

  181. </pre><p>

  182.    Now, we have a table <code class="structname">test</code> populated with data describing

  183.    the hierarchy shown below:

  184.   </p><pre class="literallayout">

  185.                         Top

  186.                      /   |  \

  187.              Science Hobbies Collections

  188.                  /       |              \

  189.         Astronomy   Amateurs_Astronomy Pictures

  190.            /  \                            |

  191. Astrophysics  Cosmology                Astronomy

  192.                                         /  |    \

  193.                                  Galaxies Stars Astronauts

  194. </pre><p>

  195.    We can do inheritance:

  196. </p><pre class="screen">

  197. ltreetest=&gt; SELECT path FROM test WHERE path &lt;@ 'Top.Science';

  198.                 path

  199. ------------------------------------

  200.  Top.Science

  201.  Top.Science.Astronomy

  202.  Top.Science.Astronomy.Astrophysics

  203.  Top.Science.Astronomy.Cosmology

  204. (4 rows)

  205. </pre><p>

  206.   </p><p>

  207.    Here are some examples of path matching:

  208. </p><pre class="screen">

  209. ltreetest=&gt; SELECT path FROM test WHERE path ~ '*.Astronomy.*';

  210.                      path

  211. -----------------------------------------------

  212.  Top.Science.Astronomy

  213.  Top.Science.Astronomy.Astrophysics

  214.  Top.Science.Astronomy.Cosmology

  215.  Top.Collections.Pictures.Astronomy

  216.  Top.Collections.Pictures.Astronomy.Stars

  217.  Top.Collections.Pictures.Astronomy.Galaxies

  218.  Top.Collections.Pictures.Astronomy.Astronauts

  219. (7 rows)

  220. 

  221. ltreetest=&gt; SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*';

  222.                 path

  223. ------------------------------------

  224.  Top.Science.Astronomy

  225.  Top.Science.Astronomy.Astrophysics

  226.  Top.Science.Astronomy.Cosmology

  227. (3 rows)

  228. </pre><p>

  229.   </p><p>

  230.    Here are some examples of full text search:

  231. </p><pre class="screen">

  232. ltreetest=&gt; SELECT path FROM test WHERE path @ 'Astro*% &amp; !pictures@';

  233.                 path

  234. ------------------------------------

  235.  Top.Science.Astronomy

  236.  Top.Science.Astronomy.Astrophysics

  237.  Top.Science.Astronomy.Cosmology

  238.  Top.Hobbies.Amateurs_Astronomy

  239. (4 rows)

  240. 

  241. ltreetest=&gt; SELECT path FROM test WHERE path @ 'Astro* &amp; !pictures@';

  242.                 path

  243. ------------------------------------

  244.  Top.Science.Astronomy

  245.  Top.Science.Astronomy.Astrophysics

  246.  Top.Science.Astronomy.Cosmology

  247. (3 rows)

  248. </pre><p>

  249.   </p><p>

  250.    Path construction using functions:

  251. </p><pre class="screen">

  252. ltreetest=&gt; SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path &lt;@ 'Top.Science.Astronomy';

  253.                  ?column?

  254. ------------------------------------------

  255.  Top.Science.Space.Astronomy

  256.  Top.Science.Space.Astronomy.Astrophysics

  257.  Top.Science.Space.Astronomy.Cosmology

  258. (3 rows)

  259. </pre><p>

  260.   </p><p>

  261.    We could simplify this by creating a SQL function that inserts a label

  262.    at a specified position in a path:

  263. </p><pre class="screen">

  264. CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree

  265.     AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'

  266.     LANGUAGE SQL IMMUTABLE;

  267. 

  268. ltreetest=&gt; SELECT ins_label(path,2,'Space') FROM test WHERE path &lt;@ 'Top.Science.Astronomy';

  269.                 ins_label

  270. ------------------------------------------

  271.  Top.Science.Space.Astronomy

  272.  Top.Science.Space.Astronomy.Astrophysics

  273.  Top.Science.Space.Astronomy.Cosmology

  274. (3 rows)

  275. </pre><p>

  276.   </p></div><div class="sect2" id="id-1.11.7.30.8"><div class="titlepage"><div><div><h3 class="title">F.21.5. Transforms</h3></div></div></div><p>

  277.    Additional extensions are available that implement transforms for

  278.    the <code class="type">ltree</code> type for PL/Python.  The extensions are

  279.    called <code class="literal">ltree_plpythonu</code>, <code class="literal">ltree_plpython2u</code>,

  280.    and <code class="literal">ltree_plpython3u</code>

  281.    (see <a class="xref" href="plpython-python23.html" title="45.1. Python 2 vs. Python 3">Section 45.1</a> for the PL/Python naming

  282.    convention).  If you install these transforms and specify them when

  283.    creating a function, <code class="type">ltree</code> values are mapped to Python lists.

  284.    (The reverse is currently not supported, however.)

  285.   </p><div class="caution"><h3 class="title">Caution</h3><p>

  286.     It is strongly recommended that the transform extensions be installed in

  287.     the same schema as <code class="filename">ltree</code>.  Otherwise there are

  288.     installation-time security hazards if a transform extension's schema

  289.     contains objects defined by a hostile user.

  290.    </p></div></div><div class="sect2" id="id-1.11.7.30.9"><div class="titlepage"><div><div><h3 class="title">F.21.6. Authors</h3></div></div></div><p>

  291.    All work was done by Teodor Sigaev (<code class="email">&lt;<a class="email" href="mailto:teodor@stack.net">teodor@stack.net</a>&gt;</code>) and

  292.    Oleg Bartunov (<code class="email">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</code>). See

  293.    <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for

  294.    additional information. Authors would like to thank Eugeny Rodichev for

  295.    helpful discussions. Comments and bug reports are welcome.

  296.   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="lo.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pageinspect.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.20. lo </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.22. pageinspect</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1