<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!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 Vsnapshot" /><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 11.5 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> This module implements a data type <code class="type">ltree</code> for representing labels of data stored in a hierarchical tree-like structure. Extensive facilities for searching through label trees are provided. </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> A <em class="firstterm">label</em> is a sequence of alphanumeric characters and underscores (for example, in C locale the characters <code class="literal">A-Za-z0-9_</code> are allowed). Labels must be less than 256 bytes long. </p><p> Examples: <code class="literal">42</code>, <code class="literal">Personal_Services</code> </p><p> A <em class="firstterm">label path</em> is a sequence of zero or more labels separated by dots, for example <code class="literal">L1.L2.L3</code>, representing a path from the root of a hierarchical tree to a particular node. The length of a label path must be less than 65kB, but keeping it under 2kB is preferable. </p><p> Example: <code class="literal">Top.Countries.Europe.Russia</code> </p><p> The <code class="filename">ltree</code> module provides several data types: </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> <code class="type">ltree</code> stores a label path. </p></li><li class="listitem"><p> <code class="type">lquery</code> represents a regular-expression-like pattern for matching <code class="type">ltree</code> values. A simple word matches that label within a path. A star symbol (<code class="literal">*</code>) matches zero or more labels. For example: </p><pre class="synopsis"> foo <em class="lineannotation"><span class="lineannotation">Match the exact label path <code class="literal">foo</code></span></em> *.foo.* <em class="lineannotation"><span class="lineannotation">Match any label path containing the label <code class="literal">foo</code></span></em> *.foo <em class="lineannotation"><span class="lineannotation">Match any label path whose last label is <code class="literal">foo</code></span></em> </pre><p> </p><p> Star symbols can also be quantified to restrict how many labels they can match: </p><pre class="synopsis"> *{<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> *{<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> *{<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> *{,<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>} </pre><p> </p><p> There are several modifiers that can be put at the end of a non-star label in <code class="type">lquery</code> to make it match more than just the exact match: </p><pre class="synopsis"> @ <em class="lineannotation"><span class="lineannotation">Match case-insensitively, for example <code class="literal">a@</code> matches <code class="literal">A</code></span></em> * <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> % <em class="lineannotation"><span class="lineannotation">Match initial underscore-separated words</span></em> </pre><p> The behavior of <code class="literal">%</code> is a bit complicated. It tries to match words rather than the entire label. For example <code class="literal">foo_bar%</code> matches <code class="literal">foo_bar_baz</code> but not <code class="literal">foo_barbaz</code>. If combined with <code class="literal">*</code>, prefix matching applies to each word separately, for example <code class="literal">foo_bar%*</code> matches <code class="literal">foo1_bar2_baz</code> but not <code class="literal">foo1_br2_baz</code>. </p><p> Also, you can write several possibly-modified labels separated with <code class="literal">|</code> (OR) to match any of those labels, and you can put <code class="literal">!</code> (NOT) at the start to match any label that doesn't match any of the alternatives. </p><p> Here's an annotated example of <code class="type">lquery</code>: </p><pre class="programlisting"> Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain a. b. c. d. e. </pre><p> This query will match any label path that: </p><div class="orderedlist"><ol class="orderedlist" type="a"><li class="listitem"><p> begins with the label <code class="literal">Top</code> </p></li><li class="listitem"><p> and next has zero to two labels before </p></li><li class="listitem"><p> a label beginning with the case-insensitive prefix <code class="literal">sport</code> </p></li><li class="listitem"><p> then a label not matching <code class="literal">football</code> nor <code class="literal">tennis</code> </p></li><li class="listitem"><p> and then ends with a label beginning with <code class="literal">Russ</code> or exactly matching <code class="literal">Spain</code>. </p></li></ol></div></li><li class="listitem"><p><code class="type">ltxtquery</code> represents a full-text-search-like pattern for matching <code class="type">ltree</code> values. An <code class="type">ltxtquery</code> value contains words, possibly with the modifiers <code class="literal">@</code>, <code class="literal">*</code>, <code class="literal">%</code> at the end; the modifiers have the same meanings as in <code class="type">lquery</code>. Words can be combined with <code class="literal">&</code> (AND), <code class="literal">|</code> (OR), <code class="literal">!</code> (NOT), and parentheses. The key difference from <code class="type">lquery</code> is that <code class="type">ltxtquery</code> matches words without regard to their position in the label path. </p><p> Here's an example <code class="type">ltxtquery</code>: </p><pre class="programlisting"> Europe & Russia*@ & !Transportation </pre><p> This will match paths that contain the label <code class="literal">Europe</code> and any label beginning with <code class="literal">Russia</code> (case-insensitive), but not paths containing the label <code class="literal">Transportation</code>. The location of these words within the path is not important. Also, when <code class="literal">%</code> is used, the word can be matched to any underscore-separated word within a label, regardless of position. </p></li></ul></div><p> Note: <code class="type">ltxtquery</code> allows whitespace between symbols, but <code class="type">ltree</code> and <code class="type">lquery</code> do not. </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> Type <code class="type">ltree</code> has the usual comparison operators <code class="literal">=</code>, <code class="literal"><></code>, <code class="literal"><</code>, <code class="literal">></code>, <code class="literal"><=</code>, <code class="literal">>=</code>. Comparison sorts in the order of a tree traversal, with the children of a node sorted by label text. In addition, the specialized operators shown in <a class="xref" href="ltree.html#LTREE-OP-TABLE" title="Table F.13. ltree Operators">Table F.13</a> are available. </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">@></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"><@</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">@></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"><@</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"><@</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">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">?@></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">?<@</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> The operators <code class="literal"><@</code>, <code class="literal">@></code>, <code class="literal">@</code> and <code class="literal">~</code> have analogues <code class="literal">^<@</code>, <code class="literal">^@></code>, <code class="literal">^@</code>, <code class="literal">^~</code>, which are the same except they do not use indexes. These are useful only for testing purposes. </p><p> 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>. </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 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 <em class="parameter"><code>offset</code></em>, length <em class="parameter"><code>len</code></em>. If <em class="parameter"><code>offset</code></em> is negative, subpath starts that far from the end of the path. If <em class="parameter"><code>len</code></em> is negative, leaves that many 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 <em class="parameter"><code>offset</code></em>, extending to end of path. If <em class="parameter"><code>offset</code></em> is negative, subpath starts that far from the 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 <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 <em class="parameter"><code>a</code></em>, searching starting at <em class="parameter"><code>offset</code></em>; negative <em class="parameter"><code>offset</code></em> means start <em class="parameter"><code>-offset</code></em> 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 (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> <code class="filename">ltree</code> supports several types of indexes that can speed up the indicated operators: </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> B-tree index over <code class="type">ltree</code>: <code class="literal"><</code>, <code class="literal"><=</code>, <code class="literal">=</code>, <code class="literal">>=</code>, <code class="literal">></code> </p></li><li class="listitem"><p> GiST index over <code class="type">ltree</code>: <code class="literal"><</code>, <code class="literal"><=</code>, <code class="literal">=</code>, <code class="literal">>=</code>, <code class="literal">></code>, <code class="literal">@></code>, <code class="literal"><@</code>, <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code> </p><p> Example of creating such an index: </p><pre class="programlisting"> CREATE INDEX path_gist_idx ON test USING GIST (path); </pre></li><li class="listitem"><p> GiST index over <code class="type">ltree[]</code>: <code class="literal">ltree[] <@ ltree</code>, <code class="literal">ltree @> ltree[]</code>, <code class="literal">@</code>, <code class="literal">~</code>, <code class="literal">?</code> </p><p> Example of creating such an index: </p><pre class="programlisting"> CREATE INDEX path_gist_idx ON test USING GIST (array_path); </pre><p> Note: This index type is lossy. </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> This example uses the following data (also available in file <code class="filename">contrib/ltree/ltreetest.sql</code> in the source distribution): </p><pre class="programlisting"> CREATE TABLE test (path ltree); INSERT INTO test VALUES ('Top'); INSERT INTO test VALUES ('Top.Science'); INSERT INTO test VALUES ('Top.Science.Astronomy'); INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics'); INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology'); INSERT INTO test VALUES ('Top.Hobbies'); INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy'); INSERT INTO test VALUES ('Top.Collections'); INSERT INTO test VALUES ('Top.Collections.Pictures'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies'); INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts'); CREATE INDEX path_gist_idx ON test USING GIST (path); CREATE INDEX path_idx ON test USING BTREE (path); </pre><p> Now, we have a table <code class="structname">test</code> populated with data describing the hierarchy shown below: </p><pre class="literallayout"> Top / | \ Science Hobbies Collections / | \ Astronomy Amateurs_Astronomy Pictures / \ | Astrophysics Cosmology Astronomy / | \ Galaxies Stars Astronauts </pre><p> We can do inheritance: </p><pre class="screen"> ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science'; path ------------------------------------ Top.Science Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (4 rows) </pre><p> </p><p> Here are some examples of path matching: </p><pre class="screen"> ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*'; path ----------------------------------------------- Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Collections.Pictures.Astronomy Top.Collections.Pictures.Astronomy.Stars Top.Collections.Pictures.Astronomy.Galaxies Top.Collections.Pictures.Astronomy.Astronauts (7 rows) ltreetest=> SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (3 rows) </pre><p> </p><p> Here are some examples of full text search: </p><pre class="screen"> ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology Top.Hobbies.Amateurs_Astronomy (4 rows) ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@'; path ------------------------------------ Top.Science.Astronomy Top.Science.Astronomy.Astrophysics Top.Science.Astronomy.Cosmology (3 rows) </pre><p> </p><p> Path construction using functions: </p><pre class="screen"> ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy'; ?column? ------------------------------------------ Top.Science.Space.Astronomy Top.Science.Space.Astronomy.Astrophysics Top.Science.Space.Astronomy.Cosmology (3 rows) </pre><p> </p><p> We could simplify this by creating a SQL function that inserts a label at a specified position in a path: </p><pre class="screen"> CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);' LANGUAGE SQL IMMUTABLE; ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy'; ins_label ------------------------------------------ Top.Science.Space.Astronomy Top.Science.Space.Astronomy.Astrophysics Top.Science.Space.Astronomy.Cosmology (3 rows) </pre><p> </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> Additional extensions are available that implement transforms for the <code class="type">ltree</code> type for PL/Python. The extensions are called <code class="literal">ltree_plpythonu</code>, <code class="literal">ltree_plpython2u</code>, and <code class="literal">ltree_plpython3u</code> (see <a class="xref" href="plpython-python23.html" title="46.1. Python 2 vs. Python 3">Section 46.1</a> for the PL/Python naming convention). If you install these transforms and specify them when creating a function, <code class="type">ltree</code> values are mapped to Python lists. (The reverse is currently not supported, however.) </p></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> All work was done by Teodor Sigaev (<code class="email"><<a class="email" href="mailto:teodor@stack.net">teodor@stack.net</a>></code>) and Oleg Bartunov (<code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>). See <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for additional information. Authors would like to thank Eugeny Rodichev for helpful discussions. Comments and bug reports are welcome. </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>