diff options
Diffstat (limited to 'doc/src/sgml/ltree.sgml')
| -rw-r--r-- | doc/src/sgml/ltree.sgml | 861 |
1 files changed, 861 insertions, 0 deletions
diff --git a/doc/src/sgml/ltree.sgml b/doc/src/sgml/ltree.sgml new file mode 100644 index 0000000..06a983c --- /dev/null +++ b/doc/src/sgml/ltree.sgml @@ -0,0 +1,861 @@ +<!-- doc/src/sgml/ltree.sgml --> + +<sect1 id="ltree" xreflabel="ltree"> + <title>ltree</title> + + <indexterm zone="ltree"> + <primary>ltree</primary> + </indexterm> + + <para> + This module implements a data type <type>ltree</type> for representing + labels of data stored in a hierarchical tree-like structure. + Extensive facilities for searching through label trees are provided. + </para> + + <para> + This module is considered <quote>trusted</quote>, that is, it can be + installed by non-superusers who have <literal>CREATE</literal> privilege + on the current database. + </para> + + <sect2> + <title>Definitions</title> + + <para> + A <firstterm>label</firstterm> is a sequence of alphanumeric characters + and underscores (for example, in C locale the characters + <literal>A-Za-z0-9_</literal> are allowed). + Labels must be less than 256 characters long. + </para> + + <para> + Examples: <literal>42</literal>, <literal>Personal_Services</literal> + </para> + + <para> + A <firstterm>label path</firstterm> is a sequence of zero or more + labels separated by dots, for example <literal>L1.L2.L3</literal>, representing + a path from the root of a hierarchical tree to a particular node. The + length of a label path cannot exceed 65535 labels. + </para> + + <para> + Example: <literal>Top.Countries.Europe.Russia</literal> + </para> + + <para> + The <filename>ltree</filename> module provides several data types: + </para> + + <itemizedlist> + <listitem> + <para> + <type>ltree</type> stores a label path. + </para> + </listitem> + + <listitem> + <para> + <type>lquery</type> represents a regular-expression-like pattern + for matching <type>ltree</type> values. A simple word matches that + label within a path. A star symbol (<literal>*</literal>) matches zero + or more labels. These can be joined with dots to form a pattern that + must match the whole label path. For example: +<synopsis> +foo <lineannotation>Match the exact label path <literal>foo</literal></lineannotation> +*.foo.* <lineannotation>Match any label path containing the label <literal>foo</literal></lineannotation> +*.foo <lineannotation>Match any label path whose last label is <literal>foo</literal></lineannotation> +</synopsis> + </para> + + <para> + Both star symbols and simple words can be quantified to restrict how many + labels they can match: +<synopsis> +*{<replaceable>n</replaceable>} <lineannotation>Match exactly <replaceable>n</replaceable> labels</lineannotation> +*{<replaceable>n</replaceable>,} <lineannotation>Match at least <replaceable>n</replaceable> labels</lineannotation> +*{<replaceable>n</replaceable>,<replaceable>m</replaceable>} <lineannotation>Match at least <replaceable>n</replaceable> but not more than <replaceable>m</replaceable> labels</lineannotation> +*{,<replaceable>m</replaceable>} <lineannotation>Match at most <replaceable>m</replaceable> labels — same as </lineannotation>*{0,<replaceable>m</replaceable>} +foo{<replaceable>n</replaceable>,<replaceable>m</replaceable>} <lineannotation>Match at least <replaceable>n</replaceable> but not more than <replaceable>m</replaceable> occurrences of <literal>foo</literal></lineannotation> +foo{,} <lineannotation>Match any number of occurrences of <literal>foo</literal>, including zero</lineannotation> +</synopsis> + In the absence of any explicit quantifier, the default for a star symbol + is to match any number of labels (that is, <literal>{,}</literal>) while + the default for a non-star item is to match exactly once (that + is, <literal>{1}</literal>). + </para> + + <para> + There are several modifiers that can be put at the end of a non-star + <type>lquery</type> item to make it match more than just the exact match: +<synopsis> +@ <lineannotation>Match case-insensitively, for example <literal>a@</literal> matches <literal>A</literal></lineannotation> +* <lineannotation>Match any label with this prefix, for example <literal>foo*</literal> matches <literal>foobar</literal></lineannotation> +% <lineannotation>Match initial underscore-separated words</lineannotation> +</synopsis> + The behavior of <literal>%</literal> is a bit complicated. It tries to match + words rather than the entire label. For example + <literal>foo_bar%</literal> matches <literal>foo_bar_baz</literal> but not + <literal>foo_barbaz</literal>. If combined with <literal>*</literal>, prefix + matching applies to each word separately, for example + <literal>foo_bar%*</literal> matches <literal>foo1_bar2_baz</literal> but + not <literal>foo1_br2_baz</literal>. + </para> + + <para> + Also, you can write several possibly-modified non-star items separated with + <literal>|</literal> (OR) to match any of those items, and you can put + <literal>!</literal> (NOT) at the start of a non-star group to match any + label that doesn't match any of the alternatives. A quantifier, if any, + goes at the end of the group; it means some number of matches for the + group as a whole (that is, some number of labels matching or not matching + any of the alternatives). + </para> + + <para> + Here's an annotated example of <type>lquery</type>: +<programlisting> +Top.*{0,2}.sport*@.!football|tennis{1,}.Russ*|Spain +a. b. c. d. e. +</programlisting> + This query will match any label path that: + </para> + <orderedlist numeration="loweralpha"> + <listitem> + <para> + begins with the label <literal>Top</literal> + </para> + </listitem> + <listitem> + <para> + and next has zero to two labels before + </para> + </listitem> + <listitem> + <para> + a label beginning with the case-insensitive prefix <literal>sport</literal> + </para> + </listitem> + <listitem> + <para> + then has one or more labels, none of which + match <literal>football</literal> nor <literal>tennis</literal> + </para> + </listitem> + <listitem> + <para> + and then ends with a label beginning with <literal>Russ</literal> or + exactly matching <literal>Spain</literal>. + </para> + </listitem> + </orderedlist> + </listitem> + + <listitem> + <para><type>ltxtquery</type> represents a full-text-search-like + pattern for matching <type>ltree</type> values. An + <type>ltxtquery</type> value contains words, possibly with the + modifiers <literal>@</literal>, <literal>*</literal>, <literal>%</literal> at the end; + the modifiers have the same meanings as in <type>lquery</type>. + Words can be combined with <literal>&</literal> (AND), + <literal>|</literal> (OR), <literal>!</literal> (NOT), and parentheses. + The key difference from + <type>lquery</type> is that <type>ltxtquery</type> matches words without + regard to their position in the label path. + </para> + + <para> + Here's an example <type>ltxtquery</type>: +<programlisting> +Europe & Russia*@ & !Transportation +</programlisting> + This will match paths that contain the label <literal>Europe</literal> and + any label beginning with <literal>Russia</literal> (case-insensitive), + but not paths containing the label <literal>Transportation</literal>. + The location of these words within the path is not important. + Also, when <literal>%</literal> is used, the word can be matched to any + underscore-separated word within a label, regardless of position. + </para> + </listitem> + + </itemizedlist> + + <para> + Note: <type>ltxtquery</type> allows whitespace between symbols, but + <type>ltree</type> and <type>lquery</type> do not. + </para> + </sect2> + + <sect2> + <title>Operators and Functions</title> + + <para> + Type <type>ltree</type> has the usual comparison operators + <literal>=</literal>, <literal><></literal>, + <literal><</literal>, <literal>></literal>, <literal><=</literal>, <literal>>=</literal>. + 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 <xref linkend="ltree-op-table"/> are available. + </para> + + <table id="ltree-op-table"> + <title><type>ltree</type> Operators</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Operator + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>@></literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Is left argument an ancestor of right (or equal)? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal><@</literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Is left argument a descendant of right (or equal)? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>~</literal> <type>lquery</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>lquery</type> <literal>~</literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does <type>ltree</type> match <type>lquery</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>?</literal> <type>lquery[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>lquery[]</type> <literal>?</literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does <type>ltree</type> match any <type>lquery</type> in array? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>@</literal> <type>ltxtquery</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>ltxtquery</type> <literal>@</literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does <type>ltree</type> match <type>ltxtquery</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>||</literal> <type>ltree</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Concatenates <type>ltree</type> paths. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree</type> <literal>||</literal> <type>text</type> + <returnvalue>ltree</returnvalue> + </para> + <para role="func_signature"> + <type>text</type> <literal>||</literal> <type>ltree</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Converts text to <type>ltree</type> and concatenates. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>@></literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>ltree</type> <literal><@</literal> <type>ltree[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does array contain an ancestor of <type>ltree</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal><@</literal> <type>ltree</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>ltree</type> <literal>@></literal> <type>ltree[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does array contain a descendant of <type>ltree</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>~</literal> <type>lquery</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>lquery</type> <literal>~</literal> <type>ltree[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does array contain any path matching <type>lquery</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>?</literal> <type>lquery[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>lquery[]</type> <literal>?</literal> <type>ltree[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does <type>ltree</type> array contain any path matching + any <type>lquery</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>@</literal> <type>ltxtquery</type> + <returnvalue>boolean</returnvalue> + </para> + <para role="func_signature"> + <type>ltxtquery</type> <literal>@</literal> <type>ltree[]</type> + <returnvalue>boolean</returnvalue> + </para> + <para> + Does array contain any path matching <type>ltxtquery</type>? + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>?@></literal> <type>ltree</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns first array entry that is an ancestor of <type>ltree</type>, + or <literal>NULL</literal> if none. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>?<@</literal> <type>ltree</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns first array entry that is a descendant of <type>ltree</type>, + or <literal>NULL</literal> if none. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>?~</literal> <type>lquery</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns first array entry that matches <type>lquery</type>, + or <literal>NULL</literal> if none. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <type>ltree[]</type> <literal>?@</literal> <type>ltxtquery</type> + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns first array entry that matches <type>ltxtquery</type>, + or <literal>NULL</literal> if none. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + The operators <literal><@</literal>, <literal>@></literal>, + <literal>@</literal> and <literal>~</literal> have analogues + <literal>^<@</literal>, <literal>^@></literal>, <literal>^@</literal>, + <literal>^~</literal>, which are the same except they do not use + indexes. These are useful only for testing purposes. + </para> + + <para> + The available functions are shown in <xref linkend="ltree-func-table"/>. + </para> + + <table id="ltree-func-table"> + <title><type>ltree</type> Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para> + <para> + Example(s) + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>subltree</primary></indexterm> + <function>subltree</function> ( <type>ltree</type>, <parameter>start</parameter> <type>integer</type>, <parameter>end</parameter> <type>integer</type> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns subpath of <type>ltree</type> from + position <parameter>start</parameter> to + position <parameter>end</parameter>-1 (counting from 0). + </para> + <para> + <literal>subltree('Top.Child1.Child2', 1, 2)</literal> + <returnvalue>Child1</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>subpath</primary></indexterm> + <function>subpath</function> ( <type>ltree</type>, <parameter>offset</parameter> <type>integer</type>, <parameter>len</parameter> <type>integer</type> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns subpath of <type>ltree</type> starting at + position <parameter>offset</parameter>, with + length <parameter>len</parameter>. If <parameter>offset</parameter> + is negative, subpath starts that far from the end of the path. + If <parameter>len</parameter> is negative, leaves that many labels off + the end of the path. + </para> + <para> + <literal>subpath('Top.Child1.Child2', 0, 2)</literal> + <returnvalue>Top.Child1</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <function>subpath</function> ( <type>ltree</type>, <parameter>offset</parameter> <type>integer</type> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Returns subpath of <type>ltree</type> starting at + position <parameter>offset</parameter>, extending to end of path. + If <parameter>offset</parameter> is negative, subpath starts that far + from the end of the path. + </para> + <para> + <literal>subpath('Top.Child1.Child2', 1)</literal> + <returnvalue>Child1.Child2</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>nlevel</primary></indexterm> + <function>nlevel</function> ( <type>ltree</type> ) + <returnvalue>integer</returnvalue> + </para> + <para> + Returns number of labels in path. + </para> + <para> + <literal>nlevel('Top.Child1.Child2')</literal> + <returnvalue>3</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>index</primary></indexterm> + <function>index</function> ( <parameter>a</parameter> <type>ltree</type>, <parameter>b</parameter> <type>ltree</type> ) + <returnvalue>integer</returnvalue> + </para> + <para> + Returns position of first occurrence of <parameter>b</parameter> in + <parameter>a</parameter>, or -1 if not found. + </para> + <para> + <literal>index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6')</literal> + <returnvalue>6</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <function>index</function> ( <parameter>a</parameter> <type>ltree</type>, <parameter>b</parameter> <type>ltree</type>, <parameter>offset</parameter> <type>integer</type> ) + <returnvalue>integer</returnvalue> + </para> + <para> + Returns position of first occurrence of <parameter>b</parameter> + in <parameter>a</parameter>, or -1 if not found. The search starts at + position <parameter>offset</parameter>; + negative <parameter>offset</parameter> means + start <parameter>-offset</parameter> labels from the end of the path. + </para> + <para> + <literal>index('0.1.2.3.5.4.5.6.8.5.6.8', '5.6', -4)</literal> + <returnvalue>9</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>text2ltree</primary></indexterm> + <function>text2ltree</function> ( <type>text</type> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Casts <type>text</type> to <type>ltree</type>. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>ltree2text</primary></indexterm> + <function>ltree2text</function> ( <type>ltree</type> ) + <returnvalue>text</returnvalue> + </para> + <para> + Casts <type>ltree</type> to <type>text</type>. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm><primary>lca</primary></indexterm> + <function>lca</function> ( <type>ltree</type> <optional>, <type>ltree</type> <optional>, ... </optional></optional> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Computes longest common ancestor of paths + (up to 8 arguments are supported). + </para> + <para> + <literal>lca('1.2.3', '1.2.3.4.5.6')</literal> + <returnvalue>1.2</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <function>lca</function> ( <type>ltree[]</type> ) + <returnvalue>ltree</returnvalue> + </para> + <para> + Computes longest common ancestor of paths in array. + </para> + <para> + <literal>lca(array['1.2.3'::ltree,'1.2.3.4'])</literal> + <returnvalue>1.2</returnvalue> + </para></entry> + </row> + </tbody> + </tgroup> + </table> + </sect2> + + <sect2> + <title>Indexes</title> + <para> + <filename>ltree</filename> supports several types of indexes that can speed + up the indicated operators: + </para> + + <itemizedlist> + <listitem> + <para> + B-tree index over <type>ltree</type>: + <literal><</literal>, <literal><=</literal>, <literal>=</literal>, + <literal>>=</literal>, <literal>></literal> + </para> + </listitem> + <listitem> + <para> + GiST index over <type>ltree</type> (<literal>gist_ltree_ops</literal> + opclass): + <literal><</literal>, <literal><=</literal>, <literal>=</literal>, + <literal>>=</literal>, <literal>></literal>, + <literal>@></literal>, <literal><@</literal>, + <literal>@</literal>, <literal>~</literal>, <literal>?</literal> + </para> + <para> + <literal>gist_ltree_ops</literal> GiST opclass approximates a set of + path labels as a bitmap signature. Its optional integer parameter + <literal>siglen</literal> determines the + signature length in bytes. The default signature length is 8 bytes. + Valid values of signature length are between 1 and 2024 bytes. Longer + signatures lead to a more precise search (scanning a smaller fraction of the index and + fewer heap pages), at the cost of a larger index. + </para> + <para> + Example of creating such an index with the default signature length of 8 bytes: + </para> +<programlisting> +CREATE INDEX path_gist_idx ON test USING GIST (path); +</programlisting> + <para> + Example of creating such an index with a signature length of 100 bytes: + </para> +<programlisting> +CREATE INDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100)); +</programlisting> + </listitem> + <listitem> + <para> + GiST index over <type>ltree[]</type> (<literal>gist__ltree_ops</literal> + opclass): + <literal>ltree[] <@ ltree</literal>, <literal>ltree @> ltree[]</literal>, + <literal>@</literal>, <literal>~</literal>, <literal>?</literal> + </para> + <para> + <literal>gist__ltree_ops</literal> GiST opclass works similarly to + <literal>gist_ltree_ops</literal> and also takes signature length as + a parameter. The default value of <literal>siglen</literal> in + <literal>gist__ltree_ops</literal> is 28 bytes. + </para> + <para> + Example of creating such an index with the default signature length of 28 bytes: + </para> +<programlisting> +CREATE INDEX path_gist_idx ON test USING GIST (array_path); +</programlisting> + <para> + Example of creating such an index with a signature length of 100 bytes: + </para> +<programlisting> +CREATE INDEX path_gist_idx ON test USING GIST (array_path gist__ltree_ops(siglen=100)); +</programlisting> + <para> + Note: This index type is lossy. + </para> + </listitem> + </itemizedlist> + </sect2> + + <sect2> + <title>Example</title> + + <para> + This example uses the following data (also available in file + <filename>contrib/ltree/ltreetest.sql</filename> in the source distribution): + </para> + +<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); +</programlisting> + + <para> + Now, we have a table <structname>test</structname> populated with data describing + the hierarchy shown below: + </para> + +<literallayout class="monospaced"> + Top + / | \ + Science Hobbies Collections + / | \ + Astronomy Amateurs_Astronomy Pictures + / \ | +Astrophysics Cosmology Astronomy + / | \ + Galaxies Stars Astronauts +</literallayout> + + <para> + We can do inheritance: +<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) +</screen> + </para> + + <para> + Here are some examples of path matching: +<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) +</screen> + </para> + + <para> + Here are some examples of full text search: +<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) +</screen> + </para> + + <para> + Path construction using functions: +<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) +</screen> + </para> + + <para> + We could simplify this by creating a SQL function that inserts a label + at a specified position in a path: +<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) +</screen> + </para> + </sect2> + + <sect2> + <title>Transforms</title> + + <para> + Additional extensions are available that implement transforms for + the <type>ltree</type> type for PL/Python. The extensions are + called <literal>ltree_plpythonu</literal>, <literal>ltree_plpython2u</literal>, + and <literal>ltree_plpython3u</literal> + (see <xref linkend="plpython-python23"/> for the PL/Python naming + convention). If you install these transforms and specify them when + creating a function, <type>ltree</type> values are mapped to Python lists. + (The reverse is currently not supported, however.) + </para> + + <caution> + <para> + It is strongly recommended that the transform extensions be installed in + the same schema as <filename>ltree</filename>. Otherwise there are + installation-time security hazards if a transform extension's schema + contains objects defined by a hostile user. + </para> + </caution> + </sect2> + + <sect2> + <title>Authors</title> + + <para> + All work was done by Teodor Sigaev (<email>teodor@stack.net</email>) and + Oleg Bartunov (<email>oleg@sai.msu.su</email>). See + <ulink url="http://www.sai.msu.su/~megera/postgres/gist/"></ulink> for + additional information. Authors would like to thank Eugeny Rodichev for + helpful discussions. Comments and bug reports are welcome. + </para> + </sect2> + +</sect1> |