codekingpro/portable-devtools
115k
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">&</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 & Russia*@ & !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"><></code>,124 <code class="literal"><</code>, <code class="literal">></code>, <code class="literal"><=</code>, <code class="literal">>=</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">@></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"><@</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">@></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"><@</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"><@</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">@></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">?@></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">?<@</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"><@</code>, <code class="literal">@></code>,272 <code class="literal">@</code> and <code class="literal">~</code> have analogues273 <code class="literal">^<@</code>, <code class="literal">^@></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"><</code>, <code class="literal"><=</code>, <code class="literal">=</code>,407 <code class="literal">>=</code>, <code class="literal">></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"><</code>, <code class="literal"><=</code>, <code class="literal">=</code>,412 <code class="literal">>=</code>, <code class="literal">></code>,413 <code class="literal">@></code>, <code class="literal"><@</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[] <@ ltree</code>, <code class="literal">ltree @> 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=> SELECT path FROM test WHERE path <@ '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=> 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=> 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=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';524 path525------------------------------------526 Top.Science.Astronomy527 Top.Science.Astronomy.Astrophysics528 Top.Science.Astronomy.Cosmology529 Top.Hobbies.Amateurs_Astronomy530(4 rows)531 532ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !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=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ '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=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ '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"><<a class="email" href="mailto:teodor@stack.net">teodor@stack.net</a>></code>) and579 Oleg Bartunov (<code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></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>