F.21. ltree · PostgreSQL 中文文檔 9.3

# F.21\. ltree 這個模塊實現了數據類型`ltree`，該類型表示存儲在分層的樹形結構里的數據的標簽。提供大量的工具搜索標簽樹。 ## F.21.1\. 定義 _label_是一個字母數字字符和下劃線的序列（例如，在C語言環境，字符`A-Za-z0-9_`是允許的）。標簽長度必須少于256字節。示例：`42`, `Personal_Services` _label path_是零個或多個由點分隔的標簽序列，例如`L1.L2.L3`，表示由樹狀結構的根節點到具體節點的路徑。標簽路徑的長度必須少于65Kb，但是保持在2Kb以下是最好的。實際上，這不是主要限制；例如，在DMOZ目錄中的最長標簽路徑([http://www.dmoz.org](http://www.dmoz.org))大約240字節。示例：`Top.Countries.Europe.Russia` `ltree`模塊提供幾種數據類型： * `ltree`存儲標簽路徑。 * `lquery`表示一個正規表達式類似的模式以匹配`ltree`值。在一個路徑中一個單詞匹配那個標簽。星號(`*`)匹配零個或多個標簽。例如： ``` foo 準確匹配標簽路徑 `foo` *.foo.* 匹配任何包含標簽 `foo` 的標簽路徑 *.foo 匹配任何以 `foo` 結束的標簽路徑 ``` 可以量化星號來限制可以匹配多少個標簽： ``` *{n} 準確匹配 n 個標簽 *{n,} 至少匹配 n 個標簽 *{n,m} 至少匹配 n 個但不超過 m 個標簽 *{,m} 最多匹配 m 個標簽 — 和 *{0,m 相同} ``` 有幾個修飾符可以放在`lquery`中非星號標簽的后面，使其不只是匹配正好的那個： ``` @ 匹配大小寫無關，例如 a@ 匹配 `A` * 匹配任何帶有這個前綴的標簽，例如 foo* 匹配 foobar % 匹配字首下劃線分開的單詞 ``` `%`的行為稍微有點復雜。它試圖匹配單詞而不是整個標簽。例如`foo_bar%` 匹配 `foo_bar_baz`而不是`foo_barbaz`。如果與`*`結合，前綴匹配分別應用到每個單詞，例如`foo_bar%*`匹配`foo1_bar2_baz` 而不是not `foo1_br2_baz`。還有，可以寫幾個由`|` (OR)分開的標簽以匹配任意這些標簽，并且可以在開始放置`!` (NOT)以匹配任何不匹配任何可選標簽的標簽。這是一個帶有評注的`lquery`的例子： ``` Top.*{0,2}.sport*@.!football|tennis.Russ*|Spain a. b. c. d. e. ``` 這個查詢將匹配任何標簽路徑： 1. 以標簽`Top`開始 2. 然后有0到2個標簽 3. 然后是一個帶有大小寫不敏感前綴`sport`的標簽 4. 然后是一個既不匹配`football`也不匹配`tennis`的標簽 5. 然后以一個帶有`Russ`或準確匹配`Spain`開頭的標簽作為結束。 * `ltxtquery`表示一個匹配`ltree`值的全文本搜索風格模式。 `ltxtquery`值包含單詞，可能在結尾帶有修飾詞`@`, `*`, `%`，這些修飾詞的含義和在`lquery`中的含義相同。單詞可以與`&` (AND), `|` (OR), `!` (NOT) 和圓括號結合。與`lquery` 主要的不同是`ltxtquery`匹配單詞時不考慮單詞在標簽路徑中的位置。這是一個`ltxtquery`的例子： ``` Europe & Russia*@ & !Transportation ``` 這將匹配包含`Europe`標簽和以`Russia` （大小寫不敏感）開始的標簽的路徑，但是不匹配包含`Transportation` 標簽的路徑。這些單詞在路徑中的位置并不重要。還有，當使用了`%`時，該單詞能匹配標簽中任何用下劃線分隔單詞，不管位置在哪。注意：`ltxtquery`允許符號之間有空格，但是`ltree` 和 `lquery`不允許這樣做。 ## F.21.2\. 操作符和函數類型`ltree`有平常的比較操作符`=`, `<>`, `<`, `>`, `<=`, `>=`。比較的優先級以樹遍歷的順序，節點的子節點以標簽文本排序。另外，在[Table F-12](#calibre_link-1436)中顯示的專業的操作符是可用的。 **Table F-12\. `ltree` 操作符** | 操作符 | 返回 | 描述 | | --- | --- | --- | | `ltree` `@>` `ltree` | `boolean` | 左邊參數是右邊參數的上代 (或相等)? | | `ltree` `<@` `ltree` | `boolean` | 左邊參數是右邊參數的后代 (或相等)? | | `ltree` `~` `lquery` | `boolean` | `ltree` 匹配 `lquery`? | | `lquery` `~` `ltree` | `boolean` | `ltree` 匹配 `lquery`? | | `ltree` `?` `lquery[]` | `boolean` | `ltree` 匹配數組中的任意 `lquery`? | | `lquery[]` `?` `ltree` | `boolean` | `ltree` 匹配數組中的任意 `lquery`? | | `ltree` `@` `ltxtquery` | `boolean` | `ltree` 匹配 `ltxtquery`? | | `ltxtquery` `@` `ltree` | `boolean` | `ltree` 匹配 `ltxtquery`? | | `ltree` `||` `ltree` | `ltree` | 連接 `ltree` 路徑 | | `ltree` `||` `text` | `ltree` | 將文本轉化成 `ltree` 并連接 | | `text` `||` `ltree` | `ltree` | 將文本轉化成 `ltree` 并連接 | | `ltree[]` `@>` `ltree` | `boolean` | 數組包含 `ltree` 的祖先? | | `ltree` `<@` `ltree[]` | `boolean` | 數組包含 `ltree` 的祖先? | | `ltree[]` `<@` `ltree` | `boolean` | 數組包含 `ltree` 的后代? | | `ltree` `@>` `ltree[]` | `boolean` | 數組包含 `ltree` 的后代? | | `ltree[]` `~` `lquery` | `boolean` | 數據包含任何匹配 `lquery` 的路徑? | | `lquery` `~` `ltree[]` | `boolean` | 數據包含任何匹配 `lquery` 的路徑? | | `ltree[]` `?` `lquery[]` | `boolean` | `ltree` 數組包含任意路徑匹配任意 `lquery`? | | `lquery[]` `?` `ltree[]` | `boolean` | `ltree` 數組包含任意路徑匹配任意 `lquery`? | | `ltree[]` `@` `ltxtquery` | `boolean` | 數據包含任意路徑匹配 `ltxtquery`? | | `ltxtquery` `@` `ltree[]` | `boolean` | 數組包含任意路徑匹配 `ltxtquery`? | | `ltree[]` `?@>` `ltree` | `ltree` | 第一個數組條目是 `ltree` 的祖先？; 如果不是則為 NULL | | `ltree[]` `?<@` `ltree` | `ltree` | 第一個數組條目是 `ltree` 的后代？; 如果不是則為 NULL | | `ltree[]` `?~` `lquery` | `ltree` | 第一個數組條目匹配 `lquery` ？; 如果不是則為 NULL | | `ltree[]` `?@` `ltxtquery` | `ltree` | 第一個數組條目匹配 `ltxtquery` ？; 如果不是則為 NULL | 操作符 `<@`, `@>`, `@` 和 `~` 類似 `^<@`, `^@>`, `^@`, `^~`，除了不用索引之外都相同。這些只是對于測試目的有用。可用的函數顯示在 [Table F-13](#calibre_link-1437) 中。 **Table F-13\. `ltree` 函數** | 函數 | 返回類型 | 描述 | 示例 | 結果 | | --- | --- | --- | --- | --- | | `subltree(ltree, int start, int end)` | `ltree` | `ltree`的子路徑，從位置 `start` 到位置 `end`-1 (從0開始計算) | `subltree('Top.Child1.Child2',1,2)` | `Child1` | | `subpath(ltree, int offset, int len)` | `ltree` | `ltree` 的子路徑，從位置 `offset` 開始，長度為 `len`。如果 `offset` 為負值，子路徑從路徑的結尾開始。如果 `len` 是負值，那么距離路徑的結尾多少個標簽。 | `subpath('Top.Child1.Child2',0,2)` | `Top.Child1` | | `subpath(ltree, int offset)` | `ltree` | `ltree` 的子路徑，從位置 `offset` 開始，一直到路徑的結束。如果 `offset` 為負值，那么子路徑從路徑的結尾開始。 | `subpath('Top.Child1.Child2',1)` | `Child1.Child2` | | `nlevel(ltree)` | `integer` | 路徑中的標簽的個數 | `nlevel('Top.Child1.Child2')` | `3` | | `index(ltree a, ltree b)` | `integer` | `b` 在 `a` 中第一次出現的位置；如果沒有發現則為 -1 | `index('0.1.2.3.5.4.5.6.8.5.6.8','5.6')` | `6` | | `index(ltree a, ltree b, int offset)` | `integer` | `b` 在 `a` 中第一次出現的位置，從 `offset` 開始搜索；負的 `offset` 意味從路徑結尾開始 `-offset` 個標簽 | `index('0.1.2.3.5.4.5.6.8.5.6.8','5.6',-4)` | `9` | | `text2ltree(text)` | `ltree` | 將 `text` 轉換為 `ltree` | | `ltree2text(ltree)` | `text` | 將 `ltree` 轉換為 `text` | | `lca(ltree, ltree, ...)` | `ltree` | 最低的公共祖先，也就是，路徑的最長公共前綴（支持多達 8 個參數） | `lca('1.2.2.3','1.2.3.4.5.6')` | `1.2` | | `lca(ltree[])` | `ltree` | 最低的公共祖先，也就是，路徑的最長公共前綴 | `lca(array['1.2.2.3'::ltree,'1.2.3'])` | `1.2` | ## F.21.3\. 索引 `ltree` 支持索引的幾個類型，可以加速指出的操作符： * `ltree` 上的 B-tree 索引： `<`, `<=`, `=`, `>=`, `>` * `ltree` 上的 GiST 索引： `<`, `<=`, `=`, `>=`, `>`, `@>`, `<@`, `@`, `~`, `?` 創建這樣一個索引的列子： ``` CREATE INDEX path_gist_idx ON test USING GIST (path); ``` * `ltree[]` 上的 GiST 索引： `ltree[] <@ ltree`, `ltree @> ltree[]`, `@`, `~`, `?` 創建這樣一個索引的例子： ``` CREATE INDEX path_gist_idx ON test USING GIST (array_path); ``` 注意：這種索引類型損耗很大。 ## F.21.4\. 示例這個示例使用下列的數據（在源代碼發布中的`contrib/ltree/ltreetest.sql`文件中也可用）： ``` 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); ``` 現在，我們有一個表`test`，由下面顯示的數據描述層級構成： ``` Top / | \ Science Hobbies Collections / | \ Astronomy Amateurs_Astronomy Pictures / \ | Astrophysics Cosmology Astronomy / | \ Galaxies Stars Astronauts ``` 現在我們可以做繼承： ``` 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) ``` 這里是一些路徑匹配的例子： ``` 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) ``` 這里是一些全文本搜索的例子： ``` 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) ``` 路徑建造使用函數： ``` 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) ``` 我們可以通過創建一個SQL函數來簡化，該函數在路徑中的指定位置插入一個標簽： ``` 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) ``` ## F.21.5\. 作者所有工作都是 Teodor Sigaev (`<[teodor@stack.net](mailto:teodor@stack.net)>`) 和 Oleg Bartunov (`<[oleg@sai.msu.su](mailto:oleg@sai.msu.su)>`)做的。參閱 [http://www.sai.msu.su/~megera/postgres/gist/](http://www.sai.msu.su/~megera/postgres/gist/)獲取額外的信息。作者感謝 Eugeny Rodichev 的有幫助的討論。歡迎評論和報告bug。