| <!-- 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 an 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> |
