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);
19
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
20
\copy ct from 'data/ct.data'
21
SELECT * FROM crosstab2('SELECT rowid, attribute, val 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, val 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, val 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, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
43
row_name | category_1 | category_2
44
----------+------------+------------
49
SELECT * FROM crosstab3('SELECT rowid, attribute, val 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, val 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, val 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, val 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, val 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, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
85
row_name | category_1 | category_2
86
----------+------------+------------
91
SELECT * FROM crosstab3('SELECT rowid, attribute, val 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, val 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, val 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, val 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, val 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
127
-- hash based crosstab
129
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
130
NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for serial column "cth.id"
131
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
132
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
133
-- the next line is intentionally left commented and is therefore a "missing" attribute
134
-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
135
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
136
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
137
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
138
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
139
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
140
-- return attributes as plain text
141
SELECT * FROM crosstab(
142
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
143
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
144
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
145
rowid | rowdt | temperature | test_result | test_startdate | volts
146
-------+--------------------------+-------------+-------------+----------------+--------
147
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
148
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | 01 March 2003 | 3.1234
151
-- this time without rowdt
152
SELECT * FROM crosstab(
153
'SELECT rowid, attribute, val FROM cth ORDER BY 1',
154
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
155
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
156
rowid | temperature | test_result | test_startdate | volts
157
-------+-------------+-------------+----------------+--------
158
test1 | 42 | PASS | | 2.6987
159
test2 | 53 | FAIL | 01 March 2003 | 3.1234
162
-- convert attributes to specific datatypes
163
SELECT * FROM crosstab(
164
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
165
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
166
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
167
rowid | rowdt | temperature | test_result | test_startdate | volts
168
-------+--------------------------+-------------+-------------+--------------------------+--------
169
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987
170
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234
173
-- source query and category query out of sync
174
SELECT * FROM crosstab(
175
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
176
'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
177
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp);
178
rowid | rowdt | temperature | test_result | test_startdate
179
-------+--------------------------+-------------+-------------+--------------------------
180
test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS |
181
test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003
184
-- if category query generates no rows, get expected error
185
SELECT * FROM crosstab(
186
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
187
'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
188
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
189
ERROR: provided "categories" SQL must return 1 column of at least one row
190
-- if category query generates more than one column, get expected error
191
SELECT * FROM crosstab(
192
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
193
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
194
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
195
ERROR: provided "categories" SQL must return 1 column of at least one row
196
-- if source query returns zero rows, get zero rows returned
197
SELECT * FROM crosstab(
198
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
199
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
200
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
201
rowid | rowdt | temperature | test_result | test_startdate | volts
202
-------+-------+-------------+-------------+----------------+-------
205
-- if source query returns zero rows, get zero rows returned even if category query generates no rows
206
SELECT * FROM crosstab(
207
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
208
'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
209
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
210
rowid | rowdt | temperature | test_result | test_startdate | volts
211
-------+-------+-------------+-------------+----------------+-------
217
-- test connectby with text based hierarchy
218
CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
219
\copy connectby_text from 'data/connectby_text.data'
220
-- with branch, without orderby
221
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
222
keyid | parent_keyid | level | branch
223
-------+--------------+-------+---------------------
225
row4 | row2 | 1 | row2~row4
226
row6 | row4 | 2 | row2~row4~row6
227
row8 | row6 | 3 | row2~row4~row6~row8
228
row5 | row2 | 1 | row2~row5
229
row9 | row5 | 2 | row2~row5~row9
232
-- without branch, without orderby
233
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
234
keyid | parent_keyid | level
235
-------+--------------+-------
244
-- with branch, with orderby
245
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos;
246
keyid | parent_keyid | level | branch | pos
247
-------+--------------+-------+---------------------+-----
248
row2 | | 0 | row2 | 1
249
row5 | row2 | 1 | row2~row5 | 2
250
row9 | row5 | 2 | row2~row5~row9 | 3
251
row4 | row2 | 1 | row2~row4 | 4
252
row6 | row4 | 2 | row2~row4~row6 | 5
253
row8 | row6 | 3 | row2~row4~row6~row8 | 6
256
-- without branch, with orderby
257
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos;
258
keyid | parent_keyid | level | pos
259
-------+--------------+-------+-----
268
-- test connectby with int based hierarchy
269
CREATE TABLE connectby_int(keyid int, parent_keyid int);
270
\copy connectby_int from 'data/connectby_int.data'
272
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
273
keyid | parent_keyid | level | branch
274
-------+--------------+-------+---------
284
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
285
keyid | parent_keyid | level
286
-------+--------------+-------
295
-- recursion detection
296
INSERT INTO connectby_int VALUES(10,9);
297
INSERT INTO connectby_int VALUES(11,10);
298
INSERT INTO connectby_int VALUES(9,11);
299
-- should fail due to infinite recursion
300
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
301
ERROR: infinite recursion detected
302
-- infinite recursion failure avoided by depth limit
303
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);
304
keyid | parent_keyid | level | branch
305
-------+--------------+-------+-------------
312
10 | 9 | 3 | 2~5~9~10
313
11 | 10 | 4 | 2~5~9~10~11
316
-- test for falsely detected recursion
317
DROP TABLE connectby_int;
318
CREATE TABLE connectby_int(keyid int, parent_keyid int);
319
INSERT INTO connectby_int VALUES(11,NULL);
320
INSERT INTO connectby_int VALUES(10,11);
321
INSERT INTO connectby_int VALUES(111,11);
322
INSERT INTO connectby_int VALUES(1,111);
323
-- this should not fail due to recursion detection
324
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);
325
keyid | parent_keyid | level | branch
326
-------+--------------+-------+----------
329
111 | 11 | 1 | 11-111
330
1 | 111 | 2 | 11-111-1