F.36. tablefunc · PostgreSQL 中文文檔 9.3

# F.36\. tablefunc `tablefun` 擴展包括了返回表記錄(即:多行)的一系列函數 . 這些函數在數據記錄的處理和運用C函數返回多行記錄中是非常有用的 ## F.36.1\. 函數列表 [Table F-28](#calibre_link-2310) 列出了`tablefunc` 擴展提供的函數 **Table F-28\. `tablefunc`函數** | 函數 | 返回值 | 描述 | | --- | --- | --- | | `normal_rand(int numvals, float8 mean, float8 stddev)` | `setof float8` | 返回一系列正態分布的隨機值 | | `crosstab(text sql)` | `setof record` | 返回一個行名加上N列數值的二維表，其中的N由查詢產生的結果確定。 | | `crosstab`_N_`(text sql)` | `setof table_crosstab_``_N_` | 返回一個行名加上N列數值的二維表 `crosstab2`, `crosstab3`, and `crosstab4` 是系統定義的, 你可以按照下面描述的定義 `crosstab`_N_`` | | `crosstab(text source_sql, text category_sql)` | `setof record` | 返回一個二維表列值由第二個查詢指定 | | `crosstab(text sql, int N)` | `setof record` | `crosstab(text)`的廢棄版本. `_N_` 被忽略,因為列值的行數由查詢決定 | | `connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])` | `setof record` | 返回一個分層的樹狀結構 | ### F.36.1.1\. `normal_rand` ``` normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8 ``` normal函數返回一系列正態分布的隨機值(高斯分布) numvals是這個函數返回值的數目,mean時正態分布的平均值,stddev是正態分布的方差例如:這個查詢返回1000個均值為5,方差為3的值 ``` test=# SELECT * FROM normal_rand(1000, 5, 3); normal_rand ---------------------- 1.56556322244898 9.10040991424657 5.36957140345079 -0.369151492880995 0.283600703686639 . . . 4.82992125404908 9.71308014517282 2.49639286969028 (1000 rows) ``` ### F.36.1.2\. `crosstab(text)` ``` crosstab(text sql) crosstab(text sql, int N) ``` crosstab函數返回一個二維表,數據在這個表里通過橫向而非縱向列出,例如: 我們已有數據如下: ``` row1 val11 row1 val12 row1 val13 ... row2 val21 row2 val22 row2 val23 ... ``` 而我們想要按如下輸出: ``` row1 val11 val12 val13 ... row2 val21 val22 val23 ... ... ``` crosstab函數使用一個SQL查詢作為參數,返回一個給定格式的二維表 sql參數用來產生一組數據源,這個語句必須返回1個row_name列,一個category列,一個value列. N是一個廢棄的參數, 例如,一個查詢需要產生如下形式的數據: ``` row_name cat value ----------+-------+------- row1 cat1 val1 row1 cat2 val2 row1 cat3 val3 row1 cat4 val4 row2 cat1 val5 row2 cat2 val6 row2 cat3 val7 row2 cat4 val8 ``` crosstab函數返回一系列記錄,輸出列的實際名字和類型必須定義在FROM字句中,例如: ``` SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text); ``` This example produces a set something like: ``` <== value columns ==> row_name category_1 category_2 ----------+------------+------------ row1 val1 val2 row2 val5 val6 ``` FROM字句必須定義一個row_name(和你查詢返回第一列具有相同數據類型)列和一個和N個value列 (和查詢返回第三列的類型匹配). 由你來定義輸出列的名字和數量. crosstab函數對于具有相同row_name的一組數據返回一行.它使用這些行從左到右填充輸出到二維表.如果這組數據少于輸出的列數,則使用null填充, 如果有多余的行數,則額外的輸入將被忽略 SQL查詢應該一直指定ORDER BY1,2來確保輸入的數據時有序的,即, 具有相同row_name的行是連續的并且在一行的輸出中是有序的.注意crosstab 不關注第二列查詢的結果;它只是用來保證第三列的值在輸出中的排序. 這是一個完整的例子: ``` CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3'); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7'); INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8'); SELECT * FROM crosstab( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2') AS ct(row_name text, category_1 text, category_2 text, category_3 text); row_name | category_1 | category_2 | category_3 ----------+------------+------------+------------ test1 | val2 | val3 | test2 | val6 | val7 | (2 rows) ``` 可以通過定義一個crosstab函數去定義任意類型,數量的列,來避免每次在FROM 字句中制定輸出列的值和類型.這將在下一節中講解.另外一種方式是在定義視圖的 FROM字句中制定. ### F.36.1.3\. `crosstab`_N_`(text)` ``` crosstab_N_(text sql) ``` crosstabN函數是crosstab函數封裝的一個例子,在使用SELECT查詢中,你可以不指定列數和類型.tablefunc擴展定義了crosstab2,crosstab3,crosstab4函數中列的類型數量. ``` CREATE TYPE tablefunc_crosstab_N AS ( row_name TEXT, category_1 TEXT, category_2 TEXT, . . . category_N TEXT ); ``` 當輸入查詢返回text類型的row_name并且你有2,3,4列輸出值時你可以直接使用這些函數.其他情況需要像上面的定義crosstab函數一樣. 例如,上面的例子也可以像下面一樣運行. ``` SELECT * FROM crosstab3( 'select rowid, attribute, value from ct where attribute = ''att2'' or attribute = ''att3'' order by 1,2'); ``` 這些函數大部分只是為了舉例說明.你可以按照crosstab函數定義你需要的返回類型的函數.這里有兩種方法來實現: * 定義一個復合類型的輸出列類似于contrib/tablefunc/tablefunc--1.0.sql中的例子. 按照crosstab函數定義的方式,定義一個唯一函數名來接收一個text類型的參數,返回setof your_type_name. 例如,你的數據源返回一個text類型的row_name,values字段為float8類型,并且你需要5列: ``` CREATE TYPE my_crosstab_float8_5_cols AS ( my_row_name text, my_category_1 float8, my_category_2 float8, my_category_3 float8, my_category_4 float8, my_category_5 float8 ); CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text) RETURNS setof my_crosstab_float8_5_cols AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; ``` * 使用OUT參數定義返回值類型.上面的例子可以像下面定義: ``` CREATE OR REPLACE FUNCTION crosstab_float8_5_cols( IN text, OUT my_row_name text, OUT my_category_1 float8, OUT my_category_2 float8, OUT my_category_3 float8, OUT my_category_4 float8, OUT my_category_5 float8) RETURNS setof record AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT; ``` ### F.36.1.4\. `crosstab(text, text)` ``` crosstab(text source_sql, text category_sql) ``` 單參數的crosstab函數的主要限制是處理一組記錄時插入每一個值到第一個列中. 如果你想插入值到指定的數據屬性中,一些數據可能沒有該屬性的值,這樣它將不能很好的處理.兩個參數的crosstab通過提供一個指定的屬性列表能較好的處理該類問題. source_sql語句返回一組數據源.這個語句返回一個row_name列,一個category列 ,和一個value列,它也可能有一個或多個extra列.row_name必須是第一列.category和 value列必須是最后兩列.任何在在row_name和category中間的列被認為時extra列. 有相同row_name的extra列被認為是相同的. 例如,source_sql可能返回下面的一系列記錄: ``` SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1; row_name extra_col cat value ----------+------------+-----+--------- row1 extra1 cat1 val1 row1 extra1 cat2 val2 row1 extra1 cat4 val4 row2 extra2 cat1 val5 row2 extra2 cat2 val6 row2 extra2 cat3 val7 row2 extra2 cat4 val8 ``` category_sql返回categories記錄.這個語句只能返回一列.它必須最少返回一行,否則會報錯.它也不能返回重復的值,否則會報錯.category_sql必須如下面: ``` SELECT DISTINCT cat FROM foo ORDER BY 1; cat ------- cat1 cat2 cat3 cat4 ``` crosstab函數返回一系列記錄,列的名字和類型必須定義在SELECT中的FROM字句中. ``` SELECT * FROM crosstab('...', '...') AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text); ``` 這將返回如下記錄: ``` <== value columns ==> row_name extra cat1 cat2 cat3 cat4 ---------+-------+------+------+------+------ row1 extra1 val1 val2 val4 row2 extra2 val5 val6 val7 val8 ``` FROM字句必須定義類型匹配的適當數量的列.如果在source_sql中返回N列,前面的N-2 列必須匹配前N-2列.剩下的列必須和source_sql返回的列類型匹配,并且返回的行數需要和category_sql返回的相同. crosstab函數對于具有相同row_name的一組連續記錄返回一行輸出.復制這組記錄的第一行到row_name和extra列.記錄中匹配category的值填充到value列中. 如果一行的category不能匹配任何category_sql返回的結果,它的值將被忽略. 如果輸入行中的category不匹配任何category_sql的返回結果,這一個輸出列將被填充為null. 實際上source_sql一直使用ORDER BY 1去確保具有相同row_name的行連續.然而, categories排序不是必須.它實際上時為了保證匹配category_sql的記錄有序. 這有兩個完整的例子: ``` create table sales(year int, month int, qty int); insert into sales values(2007, 1, 1000); insert into sales values(2007, 2, 1500); insert into sales values(2007, 7, 500); insert into sales values(2007, 11, 1500); insert into sales values(2007, 12, 2000); insert into sales values(2008, 1, 1000); select * from crosstab( 'select year, month, qty from sales order by 1', 'select m from generate_series(1,12) m' ) as ( year int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int ); year | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------ 2007 | 1000 | 1500 | | | | | 500 | | | | 1500 | 2000 2008 | 1000 | | | | | | | | | | | (2 rows) ``` ``` CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text); INSERT INTO cth VALUES('test1','01 March 2003','temperature','42'); INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS'); INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987'); INSERT INTO cth VALUES('test2','02 March 2003','temperature','53'); INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL'); INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003'); INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234'); SELECT * FROM crosstab ( 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1' ) AS ( rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8 ); rowid | rowdt | temperature | test_result | test_startdate | volts -------+--------------------------+-------------+-------------+--------------------------+-------- test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 (2 rows) ``` 你能預定義一個函數來避免在每一個查詢中都指定輸出列的名字和類型. 查看前面部分的例子.構成這種形式的crosstab被命名為crosstab_hash. ### F.36.1.5\. `connectby` ``` connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ]) ``` connectby函數返回一個表的分層顯示.這個表必須有一個鍵值去唯一的標識一行, 并且有一個父節點鍵值去關聯每一行.connectby能從任意一行列出一個子樹 [Table F-29](#calibre_link-2311) explains the parameters. **Table F-29\. `connectby` 參數** | 參數 | 描述 | | --- | --- | | `relname` | 源表的名字 | | `keyid_fld` | 鍵值的名字 | | `parent_keyid_fld` | 父節點鍵值的名字 | | `orderby_fld` | 排序兄弟的字段名字 | | `start_with` | 鍵值開始值 | | `max_depth` | 最大深度,0表示深度無限 | | `branch_delim` | 每個分支的獨立鍵值 | 鍵值和父節點鍵值可以是相同的任意類型,注意start_with必須是一個字符串,無論key的類型. connectby函數返回一系列記錄,所以輸出列的名字和類型必須定義在SELECT的FROM字句中,例如: ``` SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int); ``` 輸出的前兩列做為現在行的鍵值和父節點的鍵值;他們必須和表的鍵值類型匹配.第三列為節點在樹中的深度必須為整形.如果給出branch_delim參數,下一列是一個text類型的分支列,最后如果給出orderby_fld參數最后一列是一個整形的自增值. branch列給出鍵值到達當前行的路徑.鍵值被branch_delim字段給出的字符分割開. 如果不想顯示分支,在每一個輸出列表中忽略branch_delim和branch參數. 如果想要排序同一個父節點的兄弟節點,指定orderby_fld參數來指定需要排序兄弟節點的字段.如果orderby_fld被指定,輸出列一定包括一個整形自增的列. 表和字段值的參數被connectby函數自動的復制到查詢語句中.因此如果名字包含特殊字符或者混合字段請使用"",你可能也需要使用模式來限定表名. 在大的表中,除非在父節點的鍵值上有一個索引,否則效率將會很差. 在鍵值中不出現branch_delim參數是很重要的,否則connectby可能錯誤的報告一個死循環的錯誤. 如果不使用branch_delim參數,為了遞歸檢查將使用~來作為默認值. 下面是一個例子: ``` CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int); INSERT INTO connectby_tree VALUES('row1',NULL, 0); INSERT INTO connectby_tree VALUES('row2','row1', 0); INSERT INTO connectby_tree VALUES('row3','row1', 0); INSERT INTO connectby_tree VALUES('row4','row2', 1); INSERT INTO connectby_tree VALUES('row5','row2', 0); INSERT INTO connectby_tree VALUES('row6','row4', 0); INSERT INTO connectby_tree VALUES('row7','row3', 0); INSERT INTO connectby_tree VALUES('row8','row6', 0); INSERT INTO connectby_tree VALUES('row9','row5', 0); -- with branch, without orderby_fld (order of results is not guaranteed) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text); keyid | parent_keyid | level | branch -------+--------------+-------+--------------------- row2 | | 0 | row2 row4 | row2 | 1 | row2~row4 row6 | row4 | 2 | row2~row4~row6 row8 | row6 | 3 | row2~row4~row6~row8 row5 | row2 | 1 | row2~row5 row9 | row5 | 2 | row2~row5~row9 (6 rows) -- without branch, without orderby_fld (order of results is not guaranteed) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int); keyid | parent_keyid | level -------+--------------+------- row2 | | 0 row4 | row2 | 1 row6 | row4 | 2 row8 | row6 | 3 row5 | row2 | 1 row9 | row5 | 2 (6 rows) -- with branch, with orderby_fld (notice that row5 comes before row4) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int); keyid | parent_keyid | level | branch | pos -------+--------------+-------+---------------------+----- row2 | | 0 | row2 | 1 row5 | row2 | 1 | row2~row5 | 2 row9 | row5 | 2 | row2~row5~row9 | 3 row4 | row2 | 1 | row2~row4 | 4 row6 | row4 | 2 | row2~row4~row6 | 5 row8 | row6 | 3 | row2~row4~row6~row8 | 6 (6 rows) -- without branch, with orderby_fld (notice that row5 comes before row4) SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int); keyid | parent_keyid | level | pos -------+--------------+-------+----- row2 | | 0 | 1 row5 | row2 | 1 | 2 row9 | row5 | 2 | 3 row4 | row2 | 1 | 4 row6 | row4 | 2 | 5 row8 | row6 | 3 | 6 (6 rows) ``` ## F.36.2\. Author Joe Conway