crosstab 及其他） #tablefunc 模組包含各種回傳表格（即多行）的函數。這些函數本身很有用，也可以作為如何編寫回傳多行的 C 函數的範例。
此模組被認為是「受信任的」，也就是說，具有目前資料庫的 CREATE 權限的非超級使用者可以安裝它。
表格 F.31 總結了 tablefunc 模組提供的函數。
表格 F.31. tablefunc 函數
normal_rand #normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
normal_rand 產生一組常態分佈的隨機值（高斯分佈）。
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)
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 參數是一個 SQL 語句，產生來源資料集。此語句必須傳回一個 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 函數宣告會回傳 setof record，因此輸出欄位的實際名稱和類型必須在呼叫 SELECT 語句的 FROM 子句中定義，例如
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
這個範例會產生類似以下的集合
<== value columns ==> row_name category_1 category_2 ----------+------------+------------ row1 val1 val2 row2 val5 val6
FROM 子句必須將輸出定義為一個 row_name 欄位（與 SQL 查詢的第一個結果欄位具有相同的資料類型），後面接著 N 個 value 欄位（與 SQL 查詢的第三個結果欄位具有相同的資料類型）。您可以設定任意數量的輸出值欄位。輸出欄位的名稱由您決定。
crosstab 函數會針對具有相同 row_name 值的每組連續輸入列產生一個輸出列。它會從左到右使用這些列的 value 欄位填寫輸出 value 欄位。如果群組中的列數少於輸出 value 欄位，則額外的輸出欄位會填入空值；如果列數較多，則會跳過額外的輸入列。
實際上，SQL 查詢應始終指定 ORDER BY 1,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)
您可以避免總是寫出 FROM 子句來定義輸出欄位，方法是設定一個自訂的 crosstab 函數，該函數已將所需的輸出列類型嵌入到其定義中。這將在下一節中描述。另一種可能性是將所需的 FROM 子句嵌入到視窗定義中。
另請參閱 psql 中的 \crosstabview 命令，該命令提供的功能與 crosstab() 類似。
crosstabN(text) #crosstabN(text sql)
crosstab 函數是如何為通用 Ncrosstab 函數設定自訂封裝器的範例，這樣您就不需要在呼叫 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 和 value 欄位，並且您想要 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 中的範例。然後定義一個唯一的函數名稱，接受一個 text 參數並回傳 setof your_type_name，但連結到相同的底層 crosstab C 函數。例如，如果您的來源資料產生 text 類型的列名稱和 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;
crosstab(text, text) #crosstab(text source_sql, text category_sql)
單參數形式的 crosstab 的主要限制是它將群組中的所有值都視為相同，將每個值插入到第一個可用的欄位中。如果您希望值欄位對應於特定的資料類別，並且某些群組可能沒有某些類別的資料，那麼這種方法效果不佳。雙參數形式的 crosstab 透過提供與輸出欄位對應的類別的明確清單來處理這種情況。
source_sql 是一個 SQL 語句，用於產生來源資料集。此語句必須回傳一個 row_name 欄位、一個 category 欄位和一個 value 欄位。它也可能有一個或多個 “額外” 欄位。row_name 欄位必須是第一個。category 和 value 欄位必須是最後兩個欄位，按照這個順序。row_name 和 category 之間的任何欄位都被視為 “額外”。“額外” 欄位預計對於具有相同 row_name 值的所有列都是相同的。
例如，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 是一個 SQL 語句，用於產生類別集。此語句必須僅回傳一個欄位。它必須產生至少一個列，否則會產生錯誤。此外，它不得產生重複的值，否則會產生錯誤。category_sql 可能類似於
SELECT DISTINCT cat FROM foo ORDER BY 1;
cat
-------
cat1
cat2
cat3
cat4
crosstab 函數宣告會回傳 setof record，因此輸出欄位的實際名稱和類型必須在呼叫 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 查詢結果中的列數完全相同。
對於每個具有相同 row_name 值的連續輸入列群組，crosstab 函式會產生一個輸出列。輸出 row_name 欄位，加上任何 “額外” 欄位，都會從群組的第一列複製。輸出 value 欄位會填入來自具有相符 category 值的列的 value 欄位。如果列的 category 與 category_sql 查詢的任何輸出都不符，則會忽略其 value。輸出欄位，其相符的類別不存在於群組的任何輸入列中，則會填入 null 值。
實際上，source_sql 查詢應始終指定 ORDER BY 1，以確保具有相同 row_name 的值集中在一起。但是，群組內類別的排序並不重要。此外，務必確保 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 的基礎 C 函式名為 crosstab_hash。
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 可以顯示從任何列下降的子樹。
表格 F.32 說明了參數。
表格 F.32. connectby 參數
| 參數 | 描述 |
|---|---|
relname |
來源關聯的名稱 |
keyid_fld |
鍵值欄位的名稱 |
parent_keyid_fld |
父項鍵值欄位的名稱 |
orderby_fld |
用於排序同層級的欄位名稱（可選） |
start_with |
要開始的列的鍵值 |
max_depth |
要下降的最大深度，或零表示無限深度 |
branch_delim |
用於在分支輸出中分隔鍵值的字串（可選） |
鍵值和父項鍵值欄位可以是任何資料類型，但它們必須是相同的類型。請注意，start_with 值必須輸入為文字字串，無論鍵值欄位的類型為何。
connectby 函式宣告為傳回 setof record，因此輸出欄位的實際名稱和類型必須在呼叫 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);
前兩個輸出欄位用於目前列的鍵值及其父列的鍵值；它們必須與表格鍵值欄位的類型相符。第三個輸出欄位是樹中的深度，必須是 integer 類型。如果給定了 branch_delim 參數，則下一個輸出欄位是分支顯示，必須是 text 類型。最後，如果給定了 orderby_fld 參數，則最後一個輸出欄位是序號，並且必須是 integer 類型。
“分支” 輸出欄位顯示到達目前列的路徑的鍵值。鍵值由指定的 branch_delim 字串分隔。如果不需要分支顯示，請省略 branch_delim 參數和輸出欄位清單中的分支欄位。
如果相同父項的同層級排序很重要，請包含 orderby_fld 參數以指定要排序同層級的欄位。此欄位可以是任何可排序的資料類型。如果且僅當指定了 orderby_fld 時，輸出欄位清單必須包含最後一個整數序號欄位。
代表表格和欄位名稱的參數會原封不動地複製到 connectby 在內部產生的 SQL 查詢中。因此，如果名稱是混合大小寫或包含特殊字元，請包含雙引號。您可能還需要對表格名稱進行架構限定。
在大型表格中，如果父項鍵值欄位上沒有索引，則效能會很差。
重要的是，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)
Joe Conway
如果您在文件中發現任何不正確、與您使用特定功能的經驗不符或需要進一步澄清的地方，請使用此表單來報告文件問題。