Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6fff9a7

Browse files
committed
The attached removes the current non-standard file
"contrib/tablefunc/tablefunc-test.sql", and adds a standard regressiontest suite to contrib/tablefunc.Joe Conway
1 parent6309033 commit6fff9a7

File tree

7 files changed

+272
-85
lines changed

7 files changed

+272
-85
lines changed

‎contrib/tablefunc/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,5 +5,6 @@ include $(top_builddir)/src/Makefile.global
55
MODULES = tablefunc
66
DATA_built = tablefunc.sql
77
DOCS = README.tablefunc
8+
REGRESS = tablefunc
89

910
include$(top_srcdir)/contrib/contrib-global.mk
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
1\N
2+
21
3+
31
4+
42
5+
52
6+
64
7+
73
8+
86
9+
95
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
row1\N
2+
row2row1
3+
row3row1
4+
row4row2
5+
row5row2
6+
row6row4
7+
row7row3
8+
row8row6
9+
row9row5

‎contrib/tablefunc/data/ct.data

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
1group1test1att1val1
2+
2group1test1att2val2
3+
3group1test1att3val3
4+
4group1test1att4val4
5+
5group1test2att1val5
6+
6group1test2att2val6
7+
7group1test2att3val7
8+
8group1test2att4val8
9+
9group2test3att1val1
10+
10group2test3att2val2
11+
11group2test3att3val3
12+
12group2test4att1val4
13+
13group2test4att2val5
14+
14group2test4att3val6
Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
--
2+
-- first, define the functions. Turn off echoing so that expected file
3+
-- does not depend on contents of seg.sql.
4+
--
5+
\set ECHO none
6+
--
7+
-- normal_rand()
8+
-- no easy way to do this for regression testing
9+
--
10+
SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
11+
avg
12+
-----
13+
250
14+
(1 row)
15+
16+
--
17+
-- crosstab()
18+
--
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+
----------+------------+------------
24+
test1 | val2 | val3
25+
test2 | val6 | val7
26+
(2 rows)
27+
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+
----------+------------+------------+------------
31+
test1 | val2 | val3 |
32+
test2 | val6 | val7 |
33+
(2 rows)
34+
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 | |
40+
(2 rows)
41+
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+
----------+------------+------------
45+
test1 | val1 | val2
46+
test2 | val5 | val6
47+
(2 rows)
48+
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
54+
(2 rows)
55+
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
61+
(2 rows)
62+
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+
----------+------------+------------
66+
test3 | val1 | val2
67+
test4 | val4 | val5
68+
(2 rows)
69+
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+
----------+------------+------------+------------
73+
test3 | val1 | val2 |
74+
test4 | val4 | val5 |
75+
(2 rows)
76+
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 | |
82+
(2 rows)
83+
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+
----------+------------+------------
87+
test3 | val1 | val2
88+
test4 | val4 | val5
89+
(2 rows)
90+
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
96+
(2 rows)
97+
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 |
103+
(2 rows)
104+
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);
106+
rowid | att1 | att2
107+
-------+------+------
108+
test1 | val1 | val2
109+
test2 | val5 | val6
110+
(2 rows)
111+
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
117+
(2 rows)
118+
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
124+
(2 rows)
125+
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'
129+
-- with branch
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+
-------+--------------+-------+---------------------
133+
row2 | | 0 | row2
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
139+
(6 rows)
140+
141+
-- without branch
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+
-------+--------------+-------
145+
row2 | | 0
146+
row4 | row2 | 1
147+
row6 | row4 | 2
148+
row8 | row6 | 3
149+
row5 | row2 | 1
150+
row9 | row5 | 2
151+
(6 rows)
152+
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'
156+
-- with branch
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+
-------+--------------+-------+---------
160+
2 | | 0 | 2
161+
4 | 2 | 1 | 2~4
162+
6 | 4 | 2 | 2~4~6
163+
8 | 6 | 3 | 2~4~6~8
164+
5 | 2 | 1 | 2~5
165+
9 | 5 | 2 | 2~5~9
166+
(6 rows)
167+
168+
-- without branch
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+
-------+--------------+-------
172+
2 | | 0
173+
4 | 2 | 1
174+
6 | 4 | 2
175+
8 | 6 | 3
176+
5 | 2 | 1
177+
9 | 5 | 2
178+
(6 rows)
179+

‎contrib/tablefunc/sql/tablefunc.sql

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
--
2+
-- first, define the functions. Turn off echoing so that expected file
3+
-- does not depend on contents of seg.sql.
4+
--
5+
\set ECHO none
6+
\itablefunc.sql
7+
\set ECHO all
8+
9+
--
10+
-- normal_rand()
11+
-- no easy way to do this for regression testing
12+
--
13+
SELECTavg(normal_rand)::intFROM normal_rand(100,250,0.2, EXTRACT(SECONDSFROMCURRENT_TIME(0))::int);
14+
15+
--
16+
-- crosstab()
17+
--
18+
createtablect(idint, rowclasstext, rowidtext, attributetext, valuetext);
19+
\copy ctfrom'data/ct.data'
20+
21+
select*from crosstab2('select rowid, attribute, value from ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') order by 1,2;');
22+
select*from crosstab3('select rowid, attribute, value from ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') order by 1,2;');
23+
select*from crosstab4('select rowid, attribute, value from ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') order by 1,2;');
24+
25+
select*from crosstab2('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;');
26+
select*from crosstab3('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;');
27+
select*from crosstab4('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;');
28+
29+
select*from crosstab2('select rowid, attribute, value from ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') order by 1,2;');
30+
select*from crosstab3('select rowid, attribute, value from ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') order by 1,2;');
31+
select*from crosstab4('select rowid, attribute, value from ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') order by 1,2;');
32+
33+
select*from crosstab2('select rowid, attribute, value from ct where rowclass =''group2'' order by 1,2;');
34+
select*from crosstab3('select rowid, attribute, value from ct where rowclass =''group2'' order by 1,2;');
35+
select*from crosstab4('select rowid, attribute, value from ct where rowclass =''group2'' order by 1,2;');
36+
37+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',2)as c(rowidtext, att1text, att2text);
38+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',3)as c(rowidtext, att1text, att2text, att3text);
39+
select*from crosstab('select rowid, attribute, value from ct where rowclass =''group1'' order by 1,2;',4)as c(rowidtext, att1text, att2text, att3text, att4text);
40+
41+
-- test connectby with text based hierarchy
42+
CREATETABLEconnectby_text(keyidtext, parent_keyidtext);
43+
\copy connectby_textfrom'data/connectby_text.data'
44+
45+
-- with branch
46+
SELECT*FROM connectby('connectby_text','keyid','parent_keyid','row2',0,'~')AS t(keyidtext, parent_keyidtext, levelint, branchtext);
47+
48+
-- without branch
49+
SELECT*FROM connectby('connectby_text','keyid','parent_keyid','row2',0)AS t(keyidtext, parent_keyidtext, levelint);
50+
51+
-- test connectby with int based hierarchy
52+
CREATETABLEconnectby_int(keyidint, parent_keyidint);
53+
\copy connectby_intfrom'data/connectby_int.data'
54+
55+
-- with branch
56+
SELECT*FROM connectby('connectby_int','keyid','parent_keyid','2',0,'~')AS t(keyidint, parent_keyidint, levelint, branchtext);
57+
58+
-- without branch
59+
SELECT*FROM connectby('connectby_int','keyid','parent_keyid','2',0)AS t(keyidint, parent_keyidint, levelint);
60+

‎contrib/tablefunc/tablefunc-test.sql

Lines changed: 0 additions & 85 deletions
This file was deleted.

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp