1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Additional Supplied Modules"
16
HREF="contrib.html"><LINK
22
HREF="oid2name.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
66
TITLE="Additional Supplied Modules"
74
>Appendix F. Additional Supplied Modules</TD
80
TITLE="Additional Supplied Modules"
108
> This module implements a data type <TT
112
labels of data stored in a hierarchical tree-like structure.
113
Extensive facilities for searching through label trees are provided.
121
>F.21.1. Definitions</A
127
> is a sequence of alphanumeric characters
128
and underscores (for example, in C locale the characters
132
> are allowed). Labels must be less than 256 bytes
141
>Personal_Services</TT
148
> is a sequence of zero or more
149
labels separated by dots, for example <TT
153
a path from the root of a hierarchical tree to a particular node. The
154
length of a label path must be less than 65Kb, but keeping it under 2Kb is
155
preferable. In practice this is not a major limitation; for example,
156
the longest label path in the DMOZ catalogue (<A
157
HREF="http://www.dmoz.org"
159
>http://www.dmoz.org</A
160
>) is about 240 bytes.
165
>Top.Countries.Europe.Russia</TT
172
> module provides several data types:
182
> stores a label path.
190
> represents a regular-expression-like pattern
194
> values. A simple word matches that
195
label within a path. A star symbol (<TT
199
or more labels. For example:
203
CLASS="LINEANNOTATION"
204
>Match the exact label path <TT
210
CLASS="LINEANNOTATION"
211
>Match any label path containing the label <TT
217
CLASS="LINEANNOTATION"
218
>Match any label path whose last label is <TT
226
> Star symbols can also be quantified to restrict how many labels
236
CLASS="LINEANNOTATION"
250
CLASS="LINEANNOTATION"
269
CLASS="LINEANNOTATION"
275
> but not more than <TT
288
CLASS="LINEANNOTATION"
294
> labels — same as </I
304
> There are several modifiers that can be put at the end of a non-star
308
> to make it match more than just the exact match:
312
CLASS="LINEANNOTATION"
313
>Match case-insensitively, for example <TT
322
CLASS="LINEANNOTATION"
323
>Match any label with this prefix, for example <TT
332
CLASS="LINEANNOTATION"
333
>Match initial underscore-separated words</I
339
> is a bit complicated. It tries to match
340
words rather than the entire label. For example
351
>. If combined with <TT
355
matching applies to each word separately, for example
369
> Also, you can write several possibly-modified labels separated with
373
> (OR) to match any of those labels, and you can put
377
> (NOT) at the start to match any label that doesn't
378
match any of the alternatives.
381
> Here's an annotated example of <TT
386
CLASS="PROGRAMLISTING"
387
>Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain
390
This query will match any label path that:
398
> begins with the label <TT
406
> and next has zero to two labels before
411
> a label beginning with the case-insensitive prefix <TT
419
> then a label not matching <TT
431
> and then ends with a label beginning with <TT
448
> represents a full-text-search-like
449
pattern for matching <TT
456
> value contains words, possibly with the
467
the modifiers have the same meanings as in <TT
471
Words can be combined with <TT
481
> (NOT), and parentheses.
482
The key difference from
489
> matches words without
490
regard to their position in the label path.
493
> Here's an example <TT
498
CLASS="PROGRAMLISTING"
499
>Europe & Russia*@ & !Transportation</PRE
501
This will match paths that contain the label <TT
505
any label beginning with <TT
508
> (case-insensitive),
509
but not paths containing the label <TT
513
The location of these words within the path is not important.
517
> is used, the word can be matched to any
518
underscore-separated word within a label, regardless of position.
526
> allows whitespace between symbols, but
542
>F.21.2. Operators and Functions</A
548
> has the usual comparison operators
569
Comparison sorts in the order of a tree traversal, with the children
570
of a node sorted by label text. In addition, there are the following
571
specialized operators:
576
NAME="LTREE-OP-TABLE"
588
><COL><COL><COL><THEAD
617
>is left argument an ancestor of right (or equal)?</TD
637
>is left argument a descendant of right (or equal)?</TD
839
> and concatenate</TD
862
> and concatenate</TD
882
>does array contain an ancestor of <TT
905
>does array contain an ancestor of <TT
928
>does array contain a descendant of <TT
951
>does array contain a descendant of <TT
974
>does array contain any path matching <TT
997
>does array contain any path matching <TT
1023
> array contain any path matching any <TT
1049
> array contain any path matching any <TT
1072
>does array contain any path matching <TT
1095
>does array contain any path matching <TT
1118
>first array entry that is an ancestor of <TT
1141
>first array entry that is a descendant of <TT
1164
>first array entry that matches <TT
1187
>first array entry that matches <TT
1223
>, which are the same except they do not use
1224
indexes. These are useful only for testing purposes.
1227
> The following functions are available:
1232
NAME="LTREE-FUNC-TABLE"
1244
><COL><COL><COL><COL><COL><THEAD
1263
>subltree(ltree, int start, int end)</CODE
1281
>-1 (counting from 0)</TD
1285
>subltree('Top.Child1.Child2',1,2)</TT
1297
>subpath(ltree, int offset, int len)</CODE
1308
> starting at position
1319
> is negative, subpath starts that far from the
1320
end of the path. If <TT
1323
> is negative, leaves that many
1324
labels off the end of the path.</TD
1328
>subpath('Top.Child1.Child2',0,2)</TT
1340
>subpath(ltree, int offset)</CODE
1351
> starting at position
1355
>, extending to end of path.
1359
> is negative, subpath starts that far from the
1360
end of the path.</TD
1364
>subpath('Top.Child1.Child2',1)</TT
1376
>nlevel(ltree)</CODE
1384
>number of labels in path</TD
1388
>nlevel('Top.Child1.Child2')</TT
1400
>index(ltree a, ltree b)</CODE
1408
>position of first occurrence of <TT
1415
>; -1 if not found</TD
1419
>index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')</TT
1431
>index(ltree a, ltree b, int offset)</CODE
1439
>position of first occurrence of <TT
1446
>, searching starting at <TT
1457
labels from the end of the path</TD
1461
>index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)</TT
1473
>text2ltree(text)</CODE
1503
>ltree2text(ltree)</CODE
1533
>lca(ltree, ltree, ...)</CODE
1541
>lowest common ancestor, i.e., longest common prefix of paths
1542
(up to 8 arguments supported)</TD
1546
>lca('1.2.2.3','1.2.3.4.5.6')</TT
1566
>lowest common ancestor, i.e., longest common prefix of paths</TD
1570
>lca(array['1.2.2.3'::ltree,'1.2.3'])</TT
1594
> supports several types of indexes that can speed
1595
up the indicated operators:
1602
> B-tree index over <TT
1627
> GiST index over <TT
1667
> Example of creating such an index:
1670
CLASS="PROGRAMLISTING"
1671
>CREATE INDEX path_gist_idx ON test USING GIST (path);</PRE
1675
> GiST index over <TT
1681
>ltree[] <@ ltree</TT
1684
>ltree @> ltree[]</TT
1698
> Example of creating such an index:
1701
CLASS="PROGRAMLISTING"
1702
>CREATE INDEX path_gist_idx ON test USING GIST (array_path);</PRE
1704
> Note: This index type is lossy.
1718
> This example uses the following data (also available in file
1721
>contrib/ltree/ltreetest.sql</TT
1722
> in the source distribution):
1725
CLASS="PROGRAMLISTING"
1726
>CREATE TABLE test (path ltree);
1727
INSERT INTO test VALUES ('Top');
1728
INSERT INTO test VALUES ('Top.Science');
1729
INSERT INTO test VALUES ('Top.Science.Astronomy');
1730
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
1731
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
1732
INSERT INTO test VALUES ('Top.Hobbies');
1733
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
1734
INSERT INTO test VALUES ('Top.Collections');
1735
INSERT INTO test VALUES ('Top.Collections.Pictures');
1736
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
1737
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
1738
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
1739
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
1740
CREATE INDEX path_gist_idx ON test USING gist(path);
1741
CREATE INDEX path_idx ON test USING btree(path);</PRE
1743
> Now, we have a table <TT
1746
> populated with data describing
1747
the hierarchy shown below:
1750
CLASS="LITERALLAYOUT"
1753
Science Hobbies Collections
1755
Astronomy Amateurs_Astronomy Pictures
1757
Astrophysics Cosmology Astronomy
1759
Galaxies Stars Astronauts</PRE
1761
> We can do inheritance:
1764
>ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
1766
------------------------------------
1768
Top.Science.Astronomy
1769
Top.Science.Astronomy.Astrophysics
1770
Top.Science.Astronomy.Cosmology
1775
> Here are some examples of path matching:
1778
>ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
1780
-----------------------------------------------
1781
Top.Science.Astronomy
1782
Top.Science.Astronomy.Astrophysics
1783
Top.Science.Astronomy.Cosmology
1784
Top.Collections.Pictures.Astronomy
1785
Top.Collections.Pictures.Astronomy.Stars
1786
Top.Collections.Pictures.Astronomy.Galaxies
1787
Top.Collections.Pictures.Astronomy.Astronauts
1790
ltreetest=> SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*';
1792
------------------------------------
1793
Top.Science.Astronomy
1794
Top.Science.Astronomy.Astrophysics
1795
Top.Science.Astronomy.Cosmology
1800
> Here are some examples of full text search:
1803
>ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';
1805
------------------------------------
1806
Top.Science.Astronomy
1807
Top.Science.Astronomy.Astrophysics
1808
Top.Science.Astronomy.Cosmology
1809
Top.Hobbies.Amateurs_Astronomy
1812
ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@';
1814
------------------------------------
1815
Top.Science.Astronomy
1816
Top.Science.Astronomy.Astrophysics
1817
Top.Science.Astronomy.Cosmology
1822
> Path construction using functions:
1825
>ltreetest=> SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
1827
------------------------------------------
1828
Top.Science.Space.Astronomy
1829
Top.Science.Space.Astronomy.Astrophysics
1830
Top.Science.Space.Astronomy.Cosmology
1835
> We could simplify this by creating a SQL function that inserts a label
1836
at a specified position in a path:
1839
>CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
1840
AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
1841
LANGUAGE SQL IMMUTABLE;
1843
ltreetest=> SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy';
1845
------------------------------------------
1846
Top.Science.Space.Astronomy
1847
Top.Science.Space.Astronomy.Astrophysics
1848
Top.Science.Space.Astronomy.Cosmology
1862
> All work was done by Teodor Sigaev (<CODE
1865
HREF="mailto:teodor@stack.net"
1866
>teodor@stack.net</A
1869
Oleg Bartunov (<CODE
1872
HREF="mailto:oleg@sai.msu.su"
1877
HREF="http://www.sai.msu.su/~megera/postgres/gist/"
1879
>http://www.sai.msu.su/~megera/postgres/gist/</A
1881
additional information. Authors would like to thank Eugeny Rodichev for
1882
helpful discussions. Comments and bug reports are welcome.
1891
SUMMARY="Footer navigation table"
1920
HREF="oid2name.html"
b'\\ No newline at end of file'