Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
ltree.html583 linesDownload Raw Back to html
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.23. ltree — hierarchical tree-like data type</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 Vsnapshot" /><link rel="prev" href="lo.html" title="F.22. lo — manage large objects" /><link rel="next" href="oldsnapshot.html" title="F.24. old_snapshot — inspect old_snapshot_threshold state" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.23. ltree — hierarchical tree-like data type</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="lo.html" title="F.22. lo — manage large objects">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="oldsnapshot.html" title="F.24. old_snapshot — inspect old_snapshot_threshold state">Next</a></td></tr></table><hr /></div><div class="sect1" id="LTREE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.23. ltree — hierarchical tree-like data type <a href="#LTREE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ltree.html#LTREE-DEFINITIONS">F.23.1. Definitions</a></span></dt><dt><span class="sect2"><a href="ltree.html#LTREE-OPS-FUNCS">F.23.2. Operators and Functions</a></span></dt><dt><span class="sect2"><a href="ltree.html#LTREE-INDEXES">F.23.3. Indexes</a></span></dt><dt><span class="sect2"><a href="ltree.html#LTREE-EXAMPLE">F.23.4. Example</a></span></dt><dt><span class="sect2"><a href="ltree.html#LTREE-TRANSFORMS">F.23.5. Transforms</a></span></dt><dt><span class="sect2"><a href="ltree.html#LTREE-AUTHORS">F.23.6. Authors</a></span></dt></dl></div><a id="id-1.11.7.33.2" class="indexterm"></a><p>3  This module implements a data type <code class="type">ltree</code> for representing4  labels of data stored in a hierarchical tree-like structure.5  Extensive facilities for searching through label trees are provided.6 </p><p>7  This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be8  installed by non-superusers who have <code class="literal">CREATE</code> privilege9  on the current database.10 </p><div class="sect2" id="LTREE-DEFINITIONS"><div class="titlepage"><div><div><h3 class="title">F.23.1. Definitions <a href="#LTREE-DEFINITIONS" class="id_link">#</a></h3></div></div></div><p>11   A <em class="firstterm">label</em> is a sequence of alphanumeric characters,12   underscores, and hyphens. Valid alphanumeric character ranges are13   dependent on the database locale. For example, in C locale, the characters14   <code class="literal">A-Za-z0-9_-</code> are allowed.15   Labels must be no more than 1000 characters long.16  </p><p>17   Examples: <code class="literal">42</code>, <code class="literal">Personal_Services</code>18  </p><p>19   A <em class="firstterm">label path</em> is a sequence of zero or more20   labels separated by dots, for example <code class="literal">L1.L2.L3</code>, representing21   a path from the root of a hierarchical tree to a particular node.  The22   length of a label path cannot exceed 65535 labels.23  </p><p>24   Example: <code class="literal">Top.Countries.Europe.Russia</code>25  </p><p>26   The <code class="filename">ltree</code> module provides several data types:27  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>28     <code class="type">ltree</code> stores a label path.29    </p></li><li class="listitem"><p>30     <code class="type">lquery</code> represents a regular-expression-like pattern31     for matching <code class="type">ltree</code> values.  A simple word matches that32     label within a path.  A star symbol (<code class="literal">*</code>) matches zero33     or more labels.  These can be joined with dots to form a pattern that34     must match the whole label path.  For example:35</p><pre class="synopsis">36foo         <em class="lineannotation"><span class="lineannotation">Match the exact label path <code class="literal">foo</code></span></em>37*.foo.*     <em class="lineannotation"><span class="lineannotation">Match any label path containing the label <code class="literal">foo</code></span></em>38*.foo       <em class="lineannotation"><span class="lineannotation">Match any label path whose last label is <code class="literal">foo</code></span></em>39</pre><p>40    </p><p>41     Both star symbols and simple words can be quantified to restrict how many42     labels they can match:43</p><pre class="synopsis">44*{<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>45*{<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>46*{<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>47*{,<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>}48foo{<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> occurrences of <code class="literal">foo</code></span></em>49foo{,}      <em class="lineannotation"><span class="lineannotation">Match any number of occurrences of <code class="literal">foo</code>, including zero</span></em>50</pre><p>51     In the absence of any explicit quantifier, the default for a star symbol52     is to match any number of labels (that is, <code class="literal">{,}</code>) while53     the default for a non-star item is to match exactly once (that54     is, <code class="literal">{1}</code>).55    </p><p>56     There are several modifiers that can be put at the end of a non-star57     <code class="type">lquery</code> item to make it match more than just the exact match:58</p><pre class="synopsis">59@           <em class="lineannotation"><span class="lineannotation">Match case-insensitively, for example <code class="literal">a@</code> matches <code class="literal">A</code></span></em>60*           <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>61%           <em class="lineannotation"><span class="lineannotation">Match initial underscore-separated words</span></em>62</pre><p>63     The behavior of <code class="literal">%</code> is a bit complicated.  It tries to match64     words rather than the entire label.  For example65     <code class="literal">foo_bar%</code> matches <code class="literal">foo_bar_baz</code> but not66     <code class="literal">foo_barbaz</code>.  If combined with <code class="literal">*</code>, prefix67     matching applies to each word separately, for example68     <code class="literal">foo_bar%*</code> matches <code class="literal">foo1_bar2_baz</code> but69     not <code class="literal">foo1_br2_baz</code>.70    </p><p>71     Also, you can write several possibly-modified non-star items separated with72     <code class="literal">|</code> (OR) to match any of those items, and you can put73     <code class="literal">!</code> (NOT) at the start of a non-star group to match any74     label that doesn't match any of the alternatives.  A quantifier, if any,75     goes at the end of the group; it means some number of matches for the76     group as a whole (that is, some number of labels matching or not matching77     any of the alternatives).78    </p><p>79     Here's an annotated example of <code class="type">lquery</code>:80</p><pre class="programlisting">81Top.*{0,2}.sport*@.!football|tennis{1,}.Russ*|Spain82a.  b.     c.      d.                   e.83</pre><p>84     This query will match any label path that:85    </p><div class="orderedlist"><ol class="orderedlist" type="a"><li class="listitem"><p>86       begins with the label <code class="literal">Top</code>87      </p></li><li class="listitem"><p>88       and next has zero to two labels before89      </p></li><li class="listitem"><p>90       a label beginning with the case-insensitive prefix <code class="literal">sport</code>91      </p></li><li class="listitem"><p>92       then has one or more labels, none of which93       match <code class="literal">football</code> nor <code class="literal">tennis</code>94      </p></li><li class="listitem"><p>95       and then ends with a label beginning with <code class="literal">Russ</code> or96       exactly matching <code class="literal">Spain</code>.97      </p></li></ol></div></li><li class="listitem"><p><code class="type">ltxtquery</code> represents a full-text-search-like98    pattern for matching <code class="type">ltree</code> values.  An99    <code class="type">ltxtquery</code> value contains words, possibly with the100    modifiers <code class="literal">@</code>, <code class="literal">*</code>, <code class="literal">%</code> at the end;101    the modifiers have the same meanings as in <code class="type">lquery</code>.102    Words can be combined with <code class="literal">&amp;</code> (AND),103    <code class="literal">|</code> (OR), <code class="literal">!</code> (NOT), and parentheses.104    The key difference from105    <code class="type">lquery</code> is that <code class="type">ltxtquery</code> matches words without106    regard to their position in the label path.107    </p><p>108     Here's an example <code class="type">ltxtquery</code>:109</p><pre class="programlisting">110Europe &amp; Russia*@ &amp; !Transportation111</pre><p>112     This will match paths that contain the label <code class="literal">Europe</code> and113     any label beginning with <code class="literal">Russia</code> (case-insensitive),114     but not paths containing the label <code class="literal">Transportation</code>.115     The location of these words within the path is not important.116     Also, when <code class="literal">%</code> is used, the word can be matched to any117     underscore-separated word within a label, regardless of position.118    </p></li></ul></div><p>119   Note: <code class="type">ltxtquery</code> allows whitespace between symbols, but120   <code class="type">ltree</code> and <code class="type">lquery</code> do not.121  </p></div><div class="sect2" id="LTREE-OPS-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.23.2. Operators and Functions <a href="#LTREE-OPS-FUNCS" class="id_link">#</a></h3></div></div></div><p>122   Type <code class="type">ltree</code> has the usual comparison operators123   <code class="literal">=</code>, <code class="literal">&lt;&gt;</code>,124   <code class="literal">&lt;</code>, <code class="literal">&gt;</code>, <code class="literal">&lt;=</code>, <code class="literal">&gt;=</code>.125   Comparison sorts in the order of a tree traversal, with the children126   of a node sorted by label text.  In addition, the specialized127   operators shown in <a class="xref" href="ltree.html#LTREE-OP-TABLE" title="Table F.13. ltree Operators">Table F.13</a> are available.128  </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 /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">129        Operator130       </p>131       <p>132        Description133       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">134        <code class="type">ltree</code> <code class="literal">@&gt;</code> <code class="type">ltree</code>135        → <code class="returnvalue">boolean</code>136       </p>137       <p>138        Is left argument an ancestor of right (or equal)?139       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">140        <code class="type">ltree</code> <code class="literal">&lt;@</code> <code class="type">ltree</code>141        → <code class="returnvalue">boolean</code>142       </p>143       <p>144        Is left argument a descendant of right (or equal)?145       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">146        <code class="type">ltree</code> <code class="literal">~</code> <code class="type">lquery</code>147        → <code class="returnvalue">boolean</code>148       </p>149       <p class="func_signature">150        <code class="type">lquery</code> <code class="literal">~</code> <code class="type">ltree</code>151        → <code class="returnvalue">boolean</code>152       </p>153       <p>154        Does <code class="type">ltree</code> match <code class="type">lquery</code>?155       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">156        <code class="type">ltree</code> <code class="literal">?</code> <code class="type">lquery[]</code>157        → <code class="returnvalue">boolean</code>158       </p>159       <p class="func_signature">160        <code class="type">lquery[]</code> <code class="literal">?</code> <code class="type">ltree</code>161        → <code class="returnvalue">boolean</code>162       </p>163       <p>164        Does <code class="type">ltree</code> match any <code class="type">lquery</code> in array?165       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">166        <code class="type">ltree</code> <code class="literal">@</code> <code class="type">ltxtquery</code>167        → <code class="returnvalue">boolean</code>168       </p>169       <p class="func_signature">170        <code class="type">ltxtquery</code> <code class="literal">@</code> <code class="type">ltree</code>171        → <code class="returnvalue">boolean</code>172       </p>173       <p>174        Does <code class="type">ltree</code> match <code class="type">ltxtquery</code>?175       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">176        <code class="type">ltree</code> <code class="literal">||</code> <code class="type">ltree</code>177        → <code class="returnvalue">ltree</code>178       </p>179       <p>180        Concatenates <code class="type">ltree</code> paths.181       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">182        <code class="type">ltree</code> <code class="literal">||</code> <code class="type">text</code>183        → <code class="returnvalue">ltree</code>184       </p>185       <p class="func_signature">186        <code class="type">text</code> <code class="literal">||</code> <code class="type">ltree</code>187        → <code class="returnvalue">ltree</code>188       </p>189       <p>190        Converts text to <code class="type">ltree</code> and concatenates.191       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">192        <code class="type">ltree[]</code> <code class="literal">@&gt;</code> <code class="type">ltree</code>193        → <code class="returnvalue">boolean</code>194       </p>195       <p class="func_signature">196        <code class="type">ltree</code> <code class="literal">&lt;@</code> <code class="type">ltree[]</code>197        → <code class="returnvalue">boolean</code>198       </p>199       <p>200        Does array contain an ancestor of <code class="type">ltree</code>?201       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">202        <code class="type">ltree[]</code> <code class="literal">&lt;@</code> <code class="type">ltree</code>203        → <code class="returnvalue">boolean</code>204       </p>205       <p class="func_signature">206        <code class="type">ltree</code> <code class="literal">@&gt;</code> <code class="type">ltree[]</code>207        → <code class="returnvalue">boolean</code>208       </p>209       <p>210        Does array contain a descendant of <code class="type">ltree</code>?211       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">212        <code class="type">ltree[]</code> <code class="literal">~</code> <code class="type">lquery</code>213        → <code class="returnvalue">boolean</code>214       </p>215       <p class="func_signature">216        <code class="type">lquery</code> <code class="literal">~</code> <code class="type">ltree[]</code>217        → <code class="returnvalue">boolean</code>218       </p>219       <p>220        Does array contain any path matching <code class="type">lquery</code>?221       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">222        <code class="type">ltree[]</code> <code class="literal">?</code> <code class="type">lquery[]</code>223        → <code class="returnvalue">boolean</code>224       </p>225       <p class="func_signature">226        <code class="type">lquery[]</code> <code class="literal">?</code> <code class="type">ltree[]</code>227        → <code class="returnvalue">boolean</code>228       </p>229       <p>230        Does <code class="type">ltree</code> array contain any path matching231        any <code class="type">lquery</code>?232       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">233        <code class="type">ltree[]</code> <code class="literal">@</code> <code class="type">ltxtquery</code>234        → <code class="returnvalue">boolean</code>235       </p>236       <p class="func_signature">237        <code class="type">ltxtquery</code> <code class="literal">@</code> <code class="type">ltree[]</code>238        → <code class="returnvalue">boolean</code>239       </p>240       <p>241        Does array contain any path matching <code class="type">ltxtquery</code>?242       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">243        <code class="type">ltree[]</code> <code class="literal">?@&gt;</code> <code class="type">ltree</code>244        → <code class="returnvalue">ltree</code>245       </p>246       <p>247        Returns first array entry that is an ancestor of <code class="type">ltree</code>,248        or <code class="literal">NULL</code> if none.249       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">250        <code class="type">ltree[]</code> <code class="literal">?&lt;@</code> <code class="type">ltree</code>251        → <code class="returnvalue">ltree</code>252       </p>253       <p>254        Returns first array entry that is a descendant of <code class="type">ltree</code>,255        or <code class="literal">NULL</code> if none.256       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">257        <code class="type">ltree[]</code> <code class="literal">?~</code> <code class="type">lquery</code>258        → <code class="returnvalue">ltree</code>259       </p>260       <p>261        Returns first array entry that matches <code class="type">lquery</code>,262        or <code class="literal">NULL</code> if none.263       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">264        <code class="type">ltree[]</code> <code class="literal">?@</code> <code class="type">ltxtquery</code>265        → <code class="returnvalue">ltree</code>266       </p>267       <p>268        Returns first array entry that matches <code class="type">ltxtquery</code>,269        or <code class="literal">NULL</code> if none.270       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>271   The operators <code class="literal">&lt;@</code>, <code class="literal">@&gt;</code>,272   <code class="literal">@</code> and <code class="literal">~</code> have analogues273   <code class="literal">^&lt;@</code>, <code class="literal">^@&gt;</code>, <code class="literal">^@</code>,274   <code class="literal">^~</code>, which are the same except they do not use275   indexes.  These are useful only for testing purposes.276  </p><p>277   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>.278  </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 /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">279        Function280       </p>281       <p>282        Description283       </p>284       <p>285        Example(s)286       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">287        <a id="id-1.11.7.33.6.6.2.2.1.1.1.1" class="indexterm"></a>288        <code class="function">subltree</code> ( <code class="type">ltree</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code>, <em class="parameter"><code>end</code></em> <code class="type">integer</code> )289        → <code class="returnvalue">ltree</code>290       </p>291       <p>292        Returns subpath of <code class="type">ltree</code> from293        position <em class="parameter"><code>start</code></em> to294        position <em class="parameter"><code>end</code></em>-1 (counting from 0).295       </p>296       <p>297        <code class="literal">subltree('Top.Child1.Child2', 1, 2)</code>298        → <code class="returnvalue">Child1</code>299       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">300        <a id="id-1.11.7.33.6.6.2.2.2.1.1.1" class="indexterm"></a>301        <code class="function">subpath</code> ( <code class="type">ltree</code>, <em class="parameter"><code>offset</code></em> <code class="type">integer</code>, <em class="parameter"><code>len</code></em> <code class="type">integer</code> )302        → <code class="returnvalue">ltree</code>303       </p>304       <p>305        Returns subpath of <code class="type">ltree</code> starting at306        position <em class="parameter"><code>offset</code></em>, with307        length <em class="parameter"><code>len</code></em>.  If <em class="parameter"><code>offset</code></em>308        is negative, subpath starts that far from the end of the path.309        If <em class="parameter"><code>len</code></em> is negative, leaves that many labels off310        the end of the path.311       </p>312       <p>313        <code class="literal">subpath('Top.Child1.Child2', 0, 2)</code>314        → <code class="returnvalue">Top.Child1</code>315       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">316        <code class="function">subpath</code> ( <code class="type">ltree</code>, <em class="parameter"><code>offset</code></em> <code class="type">integer</code> )317        → <code class="returnvalue">ltree</code>318       </p>319       <p>320        Returns subpath of <code class="type">ltree</code> starting at321        position <em class="parameter"><code>offset</code></em>, extending to end of path.322        If <em class="parameter"><code>offset</code></em> is negative, subpath starts that far323        from the end of the path.324       </p>325       <p>326        <code class="literal">subpath('Top.Child1.Child2', 1)</code>327        → <code class="returnvalue">Child1.Child2</code>328       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">329        <a id="id-1.11.7.33.6.6.2.2.4.1.1.1" class="indexterm"></a>330        <code class="function">nlevel</code> ( <code class="type">ltree</code> )331        → <code class="returnvalue">integer</code>332       </p>333       <p>334        Returns number of labels in path.335       </p>336       <p>337        <code class="literal">nlevel('Top.Child1.Child2')</code>338        → <code class="returnvalue">3</code>339       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">340        <a id="id-1.11.7.33.6.6.2.2.5.1.1.1" class="indexterm"></a>341        <code class="function">index</code> ( <em class="parameter"><code>a</code></em> <code class="type">ltree</code>, <em class="parameter"><code>b</code></em> <code class="type">ltree</code> )342        → <code class="returnvalue">integer</code>343       </p>344       <p>345        Returns position of first occurrence of <em class="parameter"><code>b</code></em> in346        <em class="parameter"><code>a</code></em>, or -1 if not found.347       </p>348       <p>349        <code class="literal">index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6')</code>350        → <code class="returnvalue">6</code>351       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">352        <code class="function">index</code> ( <em class="parameter"><code>a</code></em> <code class="type">ltree</code>,  <em class="parameter"><code>b</code></em> <code class="type">ltree</code>, <em class="parameter"><code>offset</code></em> <code class="type">integer</code> )353        → <code class="returnvalue">integer</code>354       </p>355       <p>356        Returns position of first occurrence of <em class="parameter"><code>b</code></em>357        in <em class="parameter"><code>a</code></em>, or -1 if not found.  The search starts at358        position <em class="parameter"><code>offset</code></em>;359        negative <em class="parameter"><code>offset</code></em> means360        start <em class="parameter"><code>-offset</code></em> labels from the end of the path.361       </p>362       <p>363        <code class="literal">index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6', -4)</code>364        → <code class="returnvalue">9</code>365       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">366        <a id="id-1.11.7.33.6.6.2.2.7.1.1.1" class="indexterm"></a>367        <code class="function">text2ltree</code> ( <code class="type">text</code> )368        → <code class="returnvalue">ltree</code>369       </p>370       <p>371        Casts <code class="type">text</code> to <code class="type">ltree</code>.372       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">373        <a id="id-1.11.7.33.6.6.2.2.8.1.1.1" class="indexterm"></a>374        <code class="function">ltree2text</code> ( <code class="type">ltree</code> )375        → <code class="returnvalue">text</code>376       </p>377       <p>378        Casts <code class="type">ltree</code> to <code class="type">text</code>.379       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">380        <a id="id-1.11.7.33.6.6.2.2.9.1.1.1" class="indexterm"></a>381        <code class="function">lca</code> ( <code class="type">ltree</code> [<span class="optional">, <code class="type">ltree</code> [<span class="optional">, ... </span>]</span>] )382        → <code class="returnvalue">ltree</code>383       </p>384       <p>385        Computes longest common ancestor of paths386        (up to 8 arguments are supported).387       </p>388       <p>389        <code class="literal">lca('1.2.3', '1.2.3.4.5.6')</code>390        → <code class="returnvalue">1.2</code>391       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">392        <code class="function">lca</code> ( <code class="type">ltree[]</code> )393        → <code class="returnvalue">ltree</code>394       </p>395       <p>396        Computes longest common ancestor of paths in array.397       </p>398       <p>399        <code class="literal">lca(array['1.2.3'::ltree,'1.2.3.4'])</code>400        → <code class="returnvalue">1.2</code>401       </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="LTREE-INDEXES"><div class="titlepage"><div><div><h3 class="title">F.23.3. Indexes <a href="#LTREE-INDEXES" class="id_link">#</a></h3></div></div></div><p>402   <code class="filename">ltree</code> supports several types of indexes that can speed403   up the indicated operators:404  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>405     B-tree index over <code class="type">ltree</code>:406     <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>, <code class="literal">=</code>,407     <code class="literal">&gt;=</code>, <code class="literal">&gt;</code>408    </p></li><li class="listitem"><p>409     GiST index over <code class="type">ltree</code> (<code class="literal">gist_ltree_ops</code>410     opclass):411     <code class="literal">&lt;</code>, <code class="literal">&lt;=</code>, <code class="literal">=</code>,412     <code class="literal">&gt;=</code>, <code class="literal">&gt;</code>,413     <code class="literal">@&gt;</code>, <code class="literal">&lt;@</code>,414     <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code>415    </p><p>416     <code class="literal">gist_ltree_ops</code> GiST opclass approximates a set of417     path labels as a bitmap signature.  Its optional integer parameter418     <code class="literal">siglen</code> determines the419     signature length in bytes.  The default signature length is 8 bytes.420     The length must be a positive multiple of <code class="type">int</code> alignment421     (4 bytes on most machines)) up to 2024.  Longer422     signatures lead to a more precise search (scanning a smaller fraction of the index and423     fewer heap pages), at the cost of a larger index.424    </p><p>425     Example of creating such an index with the default signature length of 8 bytes:426    </p><pre class="programlisting">427CREATE INDEX path_gist_idx ON test USING GIST (path);428</pre><p>429     Example of creating such an index with a signature length of 100 bytes:430    </p><pre class="programlisting">431CREATE INDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100));432</pre></li><li class="listitem"><p>433     GiST index over <code class="type">ltree[]</code> (<code class="literal">gist__ltree_ops</code>434     opclass):435     <code class="literal">ltree[] &lt;@ ltree</code>, <code class="literal">ltree @&gt; ltree[]</code>,436     <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code>437    </p><p>438     <code class="literal">gist__ltree_ops</code> GiST opclass works similarly to439     <code class="literal">gist_ltree_ops</code> and also takes signature length as440     a parameter.  The default value of <code class="literal">siglen</code> in441      <code class="literal">gist__ltree_ops</code> is 28 bytes.442    </p><p>443     Example of creating such an index with the default signature length of 28 bytes:444    </p><pre class="programlisting">445CREATE INDEX path_gist_idx ON test USING GIST (array_path);446</pre><p>447     Example of creating such an index with a signature length of 100 bytes:448    </p><pre class="programlisting">449CREATE INDEX path_gist_idx ON test USING GIST (array_path gist__ltree_ops(siglen=100));450</pre><p>451     Note: This index type is lossy.452    </p></li></ul></div></div><div class="sect2" id="LTREE-EXAMPLE"><div class="titlepage"><div><div><h3 class="title">F.23.4. Example <a href="#LTREE-EXAMPLE" class="id_link">#</a></h3></div></div></div><p>453   This example uses the following data (also available in file454   <code class="filename">contrib/ltree/ltreetest.sql</code> in the source distribution):455  </p><pre class="programlisting">456CREATE TABLE test (path ltree);457INSERT INTO test VALUES ('Top');458INSERT INTO test VALUES ('Top.Science');459INSERT INTO test VALUES ('Top.Science.Astronomy');460INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');461INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');462INSERT INTO test VALUES ('Top.Hobbies');463INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');464INSERT INTO test VALUES ('Top.Collections');465INSERT INTO test VALUES ('Top.Collections.Pictures');466INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');467INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');468INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');469INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');470CREATE INDEX path_gist_idx ON test USING GIST (path);471CREATE INDEX path_idx ON test USING BTREE (path);472</pre><p>473   Now, we have a table <code class="structname">test</code> populated with data describing474   the hierarchy shown below:475  </p><pre class="literallayout">476                        Top477                     /   |  \478             Science Hobbies Collections479                 /       |              \480        Astronomy   Amateurs_Astronomy Pictures481           /  \                            |482Astrophysics  Cosmology                Astronomy483                                        /  |    \484                                 Galaxies Stars Astronauts485</pre><p>486   We can do inheritance:487</p><pre class="screen">488ltreetest=&gt; SELECT path FROM test WHERE path &lt;@ 'Top.Science';489                path490------------------------------------491 Top.Science492 Top.Science.Astronomy493 Top.Science.Astronomy.Astrophysics494 Top.Science.Astronomy.Cosmology495(4 rows)496</pre><p>497  </p><p>498   Here are some examples of path matching:499</p><pre class="screen">500ltreetest=&gt; SELECT path FROM test WHERE path ~ '*.Astronomy.*';501                     path502-----------------------------------------------503 Top.Science.Astronomy504 Top.Science.Astronomy.Astrophysics505 Top.Science.Astronomy.Cosmology506 Top.Collections.Pictures.Astronomy507 Top.Collections.Pictures.Astronomy.Stars508 Top.Collections.Pictures.Astronomy.Galaxies509 Top.Collections.Pictures.Astronomy.Astronauts510(7 rows)511 512ltreetest=&gt; SELECT path FROM test WHERE path ~ '*.!pictures@.Astronomy.*';513                path514------------------------------------515 Top.Science.Astronomy516 Top.Science.Astronomy.Astrophysics517 Top.Science.Astronomy.Cosmology518(3 rows)519</pre><p>520  </p><p>521   Here are some examples of full text search:522</p><pre class="screen">523ltreetest=&gt; SELECT path FROM test WHERE path @ 'Astro*% &amp; !pictures@';524                path525------------------------------------526 Top.Science.Astronomy527 Top.Science.Astronomy.Astrophysics528 Top.Science.Astronomy.Cosmology529 Top.Hobbies.Amateurs_Astronomy530(4 rows)531 532ltreetest=&gt; SELECT path FROM test WHERE path @ 'Astro* &amp; !pictures@';533                path534------------------------------------535 Top.Science.Astronomy536 Top.Science.Astronomy.Astrophysics537 Top.Science.Astronomy.Cosmology538(3 rows)539</pre><p>540  </p><p>541   Path construction using functions:542</p><pre class="screen">543ltreetest=&gt; SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path &lt;@ 'Top.Science.Astronomy';544                 ?column?545------------------------------------------546 Top.Science.Space.Astronomy547 Top.Science.Space.Astronomy.Astrophysics548 Top.Science.Space.Astronomy.Cosmology549(3 rows)550</pre><p>551  </p><p>552   We could simplify this by creating an SQL function that inserts a label553   at a specified position in a path:554</p><pre class="screen">555CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree556    AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'557    LANGUAGE SQL IMMUTABLE;558 559ltreetest=&gt; SELECT ins_label(path,2,'Space') FROM test WHERE path &lt;@ 'Top.Science.Astronomy';560                ins_label561------------------------------------------562 Top.Science.Space.Astronomy563 Top.Science.Space.Astronomy.Astrophysics564 Top.Science.Space.Astronomy.Cosmology565(3 rows)566</pre><p>567  </p></div><div class="sect2" id="LTREE-TRANSFORMS"><div class="titlepage"><div><div><h3 class="title">F.23.5. Transforms <a href="#LTREE-TRANSFORMS" class="id_link">#</a></h3></div></div></div><p>568   The <code class="literal">ltree_plpython3u</code> extension implements transforms for569   the <code class="type">ltree</code> type for PL/Python. If installed and specified when570   creating a function, <code class="type">ltree</code> values are mapped to Python lists.571   (The reverse is currently not supported, however.)572  </p><div class="caution"><h3 class="title">Caution</h3><p>573    It is strongly recommended that the transform extension be installed in574    the same schema as <code class="filename">ltree</code>.  Otherwise there are575    installation-time security hazards if a transform extension's schema576    contains objects defined by a hostile user.577   </p></div></div><div class="sect2" id="LTREE-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.23.6. Authors <a href="#LTREE-AUTHORS" class="id_link">#</a></h3></div></div></div><p>578   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>) and579   Oleg Bartunov (<code class="email">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</code>). See580   <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for581   additional information. Authors would like to thank Eugeny Rodichev for582   helpful discussions. Comments and bug reports are welcome.583  </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" title="F.22. lo — manage large objects">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="oldsnapshot.html" title="F.24. old_snapshot — inspect old_snapshot_threshold state">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.22. lo — manage large objects </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.24. old_snapshot — inspect <code class="literal">old_snapshot_threshold</code> state</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai