2 -- first, define the functions. Turn off echoing so that expected file
3 -- does not depend on contents of tablefunc.sql.
8 -- no easy way to do this for regression testing
10 SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
19 CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, value text);
20 \copy ct from 'data/ct.data'
21 SELECT * FROM crosstab2('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
22 row_name | category_1 | category_2
23 ----------+------------+------------
28 SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
29 row_name | category_1 | category_2 | category_3
30 ----------+------------+------------+------------
35 SELECT * FROM crosstab4('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
36 row_name | category_1 | category_2 | category_3 | category_4
37 ----------+------------+------------+------------+------------
38 test1 | val2 | val3 | |
39 test2 | val6 | val7 | |
42 SELECT * FROM crosstab2('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
43 row_name | category_1 | category_2
44 ----------+------------+------------
49 SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
50 row_name | category_1 | category_2 | category_3
51 ----------+------------+------------+------------
52 test1 | val1 | val2 | val3
53 test2 | val5 | val6 | val7
56 SELECT * FROM crosstab4('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
57 row_name | category_1 | category_2 | category_3 | category_4
58 ----------+------------+------------+------------+------------
59 test1 | val1 | val2 | val3 | val4
60 test2 | val5 | val6 | val7 | val8
63 SELECT * FROM crosstab2('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
64 row_name | category_1 | category_2
65 ----------+------------+------------
70 SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
71 row_name | category_1 | category_2 | category_3
72 ----------+------------+------------+------------
77 SELECT * FROM crosstab4('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
78 row_name | category_1 | category_2 | category_3 | category_4
79 ----------+------------+------------+------------+------------
80 test3 | val1 | val2 | |
81 test4 | val4 | val5 | |
84 SELECT * FROM crosstab2('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
85 row_name | category_1 | category_2
86 ----------+------------+------------
91 SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
92 row_name | category_1 | category_2 | category_3
93 ----------+------------+------------+------------
94 test3 | val1 | val2 | val3
95 test4 | val4 | val5 | val6
98 SELECT * FROM crosstab4('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
99 row_name | category_1 | category_2 | category_3 | category_4
100 ----------+------------+------------+------------+------------
101 test3 | val1 | val2 | val3 |
102 test4 | val4 | val5 | val6 |
105 SELECT * FROM crosstab('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text);
107 -------+------+------
112 SELECT * FROM crosstab('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text);
113 rowid | att1 | att2 | att3
114 -------+------+------+------
115 test1 | val1 | val2 | val3
116 test2 | val5 | val6 | val7
119 SELECT * FROM crosstab('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
120 rowid | att1 | att2 | att3 | att4
121 -------+------+------+------+------
122 test1 | val1 | val2 | val3 | val4
123 test2 | val5 | val6 | val7 | val8
126 -- test connectby with text based hierarchy
127 CREATE TABLE connectby_text(keyid text, parent_keyid text);
128 \copy connectby_text from 'data/connectby_text.data'
130 SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
131 keyid | parent_keyid | level | branch
132 -------+--------------+-------+---------------------
134 row4 | row2 | 1 | row2~row4
135 row6 | row4 | 2 | row2~row4~row6
136 row8 | row6 | 3 | row2~row4~row6~row8
137 row5 | row2 | 1 | row2~row5
138 row9 | row5 | 2 | row2~row5~row9
142 SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
143 keyid | parent_keyid | level
144 -------+--------------+-------
153 -- test connectby with int based hierarchy
154 CREATE TABLE connectby_int(keyid int, parent_keyid int);
155 \copy connectby_int from 'data/connectby_int.data'
157 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
158 keyid | parent_keyid | level | branch
159 -------+--------------+-------+---------
169 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
170 keyid | parent_keyid | level
171 -------+--------------+-------
180 -- recursion detection
181 INSERT INTO connectby_int VALUES(10,9);
182 INSERT INTO connectby_int VALUES(11,10);
183 INSERT INTO connectby_int VALUES(9,11);
184 -- should fail due to infinite recursion
185 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
186 ERROR: infinite recursion detected
187 -- infinite recursion failure avoided by depth limit
188 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);
189 keyid | parent_keyid | level | branch
190 -------+--------------+-------+-------------
197 10 | 9 | 3 | 2~5~9~10
198 11 | 10 | 4 | 2~5~9~10~11
201 -- test for falsely detected recursion
202 DROP TABLE connectby_int;
203 CREATE TABLE connectby_int(keyid int, parent_keyid int);
204 INSERT INTO connectby_int VALUES(11,NULL);
205 INSERT INTO connectby_int VALUES(10,11);
206 INSERT INTO connectby_int VALUES(111,11);
207 INSERT INTO connectby_int VALUES(1,111);
208 -- this should not fail due to recursion detection
209 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);
210 keyid | parent_keyid | level | branch
211 -------+--------------+-------+----------
214 111 | 11 | 1 | 11-111
215 1 | 111 | 2 | 11-111-1