← Back to branch summary

~vcs-imports/mammoth-replicator/trunk

« back to all changes in this revision

Viewing changes to contrib/tablefunc/sql/tablefunc.sql

  • Committer: alvherre
  • Date: 2005-12-16 21:24:52 UTC
  • Revision ID: svn-v4:db760fc0-0f08-0410-9d63-cc6633f64896:trunk:1
Initial import of the REL8_0_3 sources from the Pgsql CVS repository.

Show diffs side-by-side

added added

removed removed

Lines of Context:
contrib/tablefunc/sql/tablefunc.sql
 
1
--
 
2
-- first, define the functions.  Turn off echoing so that expected file
 
3
-- does not depend on contents of tablefunc.sql.
 
4
--
 
5
\set ECHO none
 
6
\i tablefunc.sql
 
7
\set ECHO all
 
8
 
 
9
--
 
10
-- normal_rand()
 
11
-- no easy way to do this for regression testing
 
12
--
 
13
SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2);
 
14
 
 
15
--
 
16
-- crosstab()
 
17
--
 
18
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
 
19
\copy ct from 'data/ct.data'
 
20
 
 
21
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 
22
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 
23
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
 
24
 
 
25
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 
26
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 
27
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 
28
 
 
29
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 
30
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 
31
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
 
32
 
 
33
SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 
34
SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 
35
SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 
36
 
 
37
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);
 
38
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);
 
39
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);
 
40
 
 
41
--
 
42
-- hash based crosstab
 
43
--
 
44
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
 
45
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
 
46
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
 
47
-- the next line is intentionally left commented and is therefore a "missing" attribute
 
48
-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
 
49
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
 
50
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
 
51
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
 
52
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
 
53
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
 
54
 
 
55
-- return attributes as plain text
 
56
SELECT * FROM crosstab(
 
57
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 
58
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 
59
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 
60
 
 
61
-- this time without rowdt
 
62
SELECT * FROM crosstab(
 
63
  'SELECT rowid, attribute, val FROM cth ORDER BY 1',
 
64
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 
65
AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
 
66
 
 
67
-- convert attributes to specific datatypes
 
68
SELECT * FROM crosstab(
 
69
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 
70
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 
71
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 
72
 
 
73
-- source query and category query out of sync
 
74
SELECT * FROM crosstab(
 
75
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 
76
  'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
 
77
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp);
 
78
 
 
79
-- if category query generates no rows, get expected error
 
80
SELECT * FROM crosstab(
 
81
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 
82
  'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
 
83
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 
84
 
 
85
-- if category query generates more than one column, get expected error
 
86
SELECT * FROM crosstab(
 
87
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 
88
  'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
 
89
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 
90
 
 
91
-- if source query returns zero rows, get zero rows returned
 
92
SELECT * FROM crosstab(
 
93
  'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
 
94
  'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 
95
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 
96
 
 
97
-- if source query returns zero rows, get zero rows returned even if category query generates no rows
 
98
SELECT * FROM crosstab(
 
99
  'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
 
100
  'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
 
101
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 
102
 
 
103
--
 
104
-- connectby
 
105
--
 
106
 
 
107
-- test connectby with text based hierarchy
 
108
CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
 
109
\copy connectby_text from 'data/connectby_text.data'
 
110
 
 
111
-- with branch, without orderby
 
112
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
 
113
 
 
114
-- without branch, without orderby
 
115
SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
 
116
 
 
117
-- with branch, with orderby
 
118
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;
 
119
 
 
120
-- without branch, with orderby
 
121
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;
 
122
 
 
123
-- test connectby with int based hierarchy
 
124
CREATE TABLE connectby_int(keyid int, parent_keyid int);
 
125
\copy connectby_int from 'data/connectby_int.data'
 
126
 
 
127
-- with branch
 
128
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 
129
 
 
130
-- without branch
 
131
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
 
132
 
 
133
-- recursion detection
 
134
INSERT INTO connectby_int VALUES(10,9);
 
135
INSERT INTO connectby_int VALUES(11,10);
 
136
INSERT INTO connectby_int VALUES(9,11);
 
137
 
 
138
-- should fail due to infinite recursion
 
139
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 
140
 
 
141
-- infinite recursion failure avoided by depth limit
 
142
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 
143
 
 
144
-- test for falsely detected recursion
 
145
DROP TABLE connectby_int;
 
146
CREATE TABLE connectby_int(keyid int, parent_keyid int);
 
147
INSERT INTO connectby_int VALUES(11,NULL);
 
148
INSERT INTO connectby_int VALUES(10,11);
 
149
INSERT INTO connectby_int VALUES(111,11);
 
150
INSERT INTO connectby_int VALUES(1,111);
 
151
-- this should not fail due to recursion detection
 
152
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);
 
153