contrib/tablefunc regression test - Mailing list pgsql-patches

The attached removes the current non-standard file
"contrib/tablefunc/tablefunc-test.sql", and adds a standard regression
test suite to contrib/tablefunc.

If there are no objections, please apply.

Thanks,

Joe
Index: contrib/tablefunc/Makefile
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/Makefile,v
retrieving revision 1.1
diff -c -r1.1 Makefile
*** contrib/tablefunc/Makefile    30 Jul 2002 16:31:11 -0000    1.1
--- contrib/tablefunc/Makefile    5 Sep 2002 22:07:58 -0000
***************
*** 5,9 ****
--- 5,10 ----
  MODULES = tablefunc
  DATA_built = tablefunc.sql
  DOCS = README.tablefunc
+ REGRESS = tablefunc

  include $(top_srcdir)/contrib/contrib-global.mk
Index: contrib/tablefunc/tablefunc-test.sql
===================================================================
RCS file: contrib/tablefunc/tablefunc-test.sql
diff -N contrib/tablefunc/tablefunc-test.sql
*** contrib/tablefunc/tablefunc-test.sql    2 Sep 2002 05:44:04 -0000    1.3
--- /dev/null    1 Jan 1970 00:00:00 -0000
***************
*** 1,85 ****
- --
- -- normal_rand()
- --
- SELECT * FROM normal_rand(100, 250, 5, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
-
- --
- -- crosstab()
- --
- create table ct(id serial, rowclass text, rowid text, attribute text, value text);
-
- insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att1','val1');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att2','val2');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att3','val3');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test1','att4','val4');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att1','val5');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att2','val6');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att3','val7');
- insert into ct(rowclass, rowid, attribute, value) values('group1','test2','att4','val8');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att1','val1');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att2','val2');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test3','att3','val3');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att1','val4');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att2','val5');
- insert into ct(rowclass, rowid, attribute, value) values('group2','test4','att3','val6');
-
- select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
- select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
- select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
-
- select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
- select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
- select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
-
- select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
- select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
- select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
-
- select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
- select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
- select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
-
- select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as
c(rowidtext, att1 text, att2 text); 
- select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as
c(rowidtext, att1 text, att2 text, att3 text); 
- select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as
c(rowidtext, att1 text, att2 text, att3 text, att4 text); 
-
- -- test connectby with text based hierarchy
- DROP TABLE connectby_tree;
- CREATE TABLE connectby_tree(keyid text, parent_keyid text);
-
- INSERT INTO connectby_tree VALUES('row1',NULL);
- INSERT INTO connectby_tree VALUES('row2','row1');
- INSERT INTO connectby_tree VALUES('row3','row1');
- INSERT INTO connectby_tree VALUES('row4','row2');
- INSERT INTO connectby_tree VALUES('row5','row2');
- INSERT INTO connectby_tree VALUES('row6','row4');
- INSERT INTO connectby_tree VALUES('row7','row3');
- INSERT INTO connectby_tree VALUES('row8','row6');
- INSERT INTO connectby_tree VALUES('row9','row5');
-
- -- with branch
- SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid
text,level int, branch text); 
-
- -- without branch
- SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text,
levelint); 
-
- -- test connectby with int based hierarchy
- DROP TABLE connectby_tree;
- CREATE TABLE connectby_tree(keyid int, parent_keyid int);
-
- INSERT INTO connectby_tree VALUES(1,NULL);
- INSERT INTO connectby_tree VALUES(2,1);
- INSERT INTO connectby_tree VALUES(3,1);
- INSERT INTO connectby_tree VALUES(4,2);
- INSERT INTO connectby_tree VALUES(5,2);
- INSERT INTO connectby_tree VALUES(6,4);
- INSERT INTO connectby_tree VALUES(7,3);
- INSERT INTO connectby_tree VALUES(8,6);
- INSERT INTO connectby_tree VALUES(9,5);
-
- -- with branch
- SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int,
levelint, branch text); 
-
- -- without branch
- SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level
int);
-
--- 0 ----
Index: contrib/tablefunc/data/connectby_int.data
===================================================================
RCS file: contrib/tablefunc/data/connectby_int.data
diff -N contrib/tablefunc/data/connectby_int.data
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- contrib/tablefunc/data/connectby_int.data    5 Sep 2002 22:06:23 -0000
***************
*** 0 ****
--- 1,9 ----
+ 1    \N
+ 2    1
+ 3    1
+ 4    2
+ 5    2
+ 6    4
+ 7    3
+ 8    6
+ 9    5
Index: contrib/tablefunc/data/connectby_text.data
===================================================================
RCS file: contrib/tablefunc/data/connectby_text.data
diff -N contrib/tablefunc/data/connectby_text.data
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- contrib/tablefunc/data/connectby_text.data    5 Sep 2002 22:04:47 -0000
***************
*** 0 ****
--- 1,9 ----
+ row1    \N
+ row2    row1
+ row3    row1
+ row4    row2
+ row5    row2
+ row6    row4
+ row7    row3
+ row8    row6
+ row9    row5
Index: contrib/tablefunc/data/ct.data
===================================================================
RCS file: contrib/tablefunc/data/ct.data
diff -N contrib/tablefunc/data/ct.data
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- contrib/tablefunc/data/ct.data    5 Sep 2002 22:17:16 -0000
***************
*** 0 ****
--- 1,14 ----
+ 1    group1    test1    att1    val1
+ 2    group1    test1    att2    val2
+ 3    group1    test1    att3    val3
+ 4    group1    test1    att4    val4
+ 5    group1    test2    att1    val5
+ 6    group1    test2    att2    val6
+ 7    group1    test2    att3    val7
+ 8    group1    test2    att4    val8
+ 9    group2    test3    att1    val1
+ 10    group2    test3    att2    val2
+ 11    group2    test3    att3    val3
+ 12    group2    test4    att1    val4
+ 13    group2    test4    att2    val5
+ 14    group2    test4    att3    val6
Index: contrib/tablefunc/expected/tablefunc.out
===================================================================
RCS file: contrib/tablefunc/expected/tablefunc.out
diff -N contrib/tablefunc/expected/tablefunc.out
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- contrib/tablefunc/expected/tablefunc.out    5 Sep 2002 23:08:45 -0000
***************
*** 0 ****
--- 1,179 ----
+ --
+ -- first, define the functions.  Turn off echoing so that expected file
+ -- does not depend on contents of seg.sql.
+ --
+ \set ECHO none
+ --
+ -- normal_rand()
+ -- no easy way to do this for regression testing
+ --
+ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
+  avg
+ -----
+  250
+ (1 row)
+
+ --
+ -- crosstab()
+ --
+ create table ct(id int, rowclass text, rowid text, attribute text, value text);
+ \copy ct from 'data/ct.data'
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+  row_name | category_1 | category_2
+ ----------+------------+------------
+  test1    | val2       | val3
+  test2    | val6       | val7
+ (2 rows)
+
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+  row_name | category_1 | category_2 | category_3
+ ----------+------------+------------+------------
+  test1    | val2       | val3       |
+  test2    | val6       | val7       |
+ (2 rows)
+
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+  row_name | category_1 | category_2 | category_3 | category_4
+ ----------+------------+------------+------------+------------
+  test1    | val2       | val3       |            |
+  test2    | val6       | val7       |            |
+ (2 rows)
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+  row_name | category_1 | category_2
+ ----------+------------+------------
+  test1    | val1       | val2
+  test2    | val5       | val6
+ (2 rows)
+
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+  row_name | category_1 | category_2 | category_3
+ ----------+------------+------------+------------
+  test1    | val1       | val2       | val3
+  test2    | val5       | val6       | val7
+ (2 rows)
+
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+  row_name | category_1 | category_2 | category_3 | category_4
+ ----------+------------+------------+------------+------------
+  test1    | val1       | val2       | val3       | val4
+  test2    | val5       | val6       | val7       | val8
+ (2 rows)
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+  row_name | category_1 | category_2
+ ----------+------------+------------
+  test3    | val1       | val2
+  test4    | val4       | val5
+ (2 rows)
+
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+  row_name | category_1 | category_2 | category_3
+ ----------+------------+------------+------------
+  test3    | val1       | val2       |
+  test4    | val4       | val5       |
+ (2 rows)
+
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+  row_name | category_1 | category_2 | category_3 | category_4
+ ----------+------------+------------+------------+------------
+  test3    | val1       | val2       |            |
+  test4    | val4       | val5       |            |
+ (2 rows)
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+  row_name | category_1 | category_2
+ ----------+------------+------------
+  test3    | val1       | val2
+  test4    | val4       | val5
+ (2 rows)
+
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+  row_name | category_1 | category_2 | category_3
+ ----------+------------+------------+------------
+  test3    | val1       | val2       | val3
+  test4    | val4       | val5       | val6
+ (2 rows)
+
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+  row_name | category_1 | category_2 | category_3 | category_4
+ ----------+------------+------------+------------+------------
+  test3    | val1       | val2       | val3       |
+  test4    | val4       | val5       | val6       |
+ (2 rows)
+
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as
c(rowidtext, att1 text, att2 text); 
+  rowid | att1 | att2
+ -------+------+------
+  test1 | val1 | val2
+  test2 | val5 | val6
+ (2 rows)
+
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as
c(rowidtext, att1 text, att2 text, att3 text); 
+  rowid | att1 | att2 | att3
+ -------+------+------+------
+  test1 | val1 | val2 | val3
+  test2 | val5 | val6 | val7
+ (2 rows)
+
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as
c(rowidtext, att1 text, att2 text, att3 text, att4 text); 
+  rowid | att1 | att2 | att3 | att4
+ -------+------+------+------+------
+  test1 | val1 | val2 | val3 | val4
+  test2 | val5 | val6 | val7 | val8
+ (2 rows)
+
+ -- test connectby with text based hierarchy
+ CREATE TABLE connectby_text(keyid text, parent_keyid text);
+ \copy connectby_text from 'data/connectby_text.data'
+ -- with branch
+ SELECT * FROM connectby('connectby_text', '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
+ SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text,
levelint); 
+  keyid | parent_keyid | level
+ -------+--------------+-------
+  row2  |              |     0
+  row4  | row2         |     1
+  row6  | row4         |     2
+  row8  | row6         |     3
+  row5  | row2         |     1
+  row9  | row5         |     2
+ (6 rows)
+
+ -- test connectby with int based hierarchy
+ CREATE TABLE connectby_int(keyid int, parent_keyid int);
+ \copy connectby_int from 'data/connectby_int.data'
+ -- with branch
+ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int,
levelint, branch text); 
+  keyid | parent_keyid | level | branch
+ -------+--------------+-------+---------
+      2 |              |     0 | 2
+      4 |            2 |     1 | 2~4
+      6 |            4 |     2 | 2~4~6
+      8 |            6 |     3 | 2~4~6~8
+      5 |            2 |     1 | 2~5
+      9 |            5 |     2 | 2~5~9
+ (6 rows)
+
+ -- without branch
+ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level
int);
+  keyid | parent_keyid | level
+ -------+--------------+-------
+      2 |              |     0
+      4 |            2 |     1
+      6 |            4 |     2
+      8 |            6 |     3
+      5 |            2 |     1
+      9 |            5 |     2
+ (6 rows)
+
Index: contrib/tablefunc/sql/tablefunc.sql
===================================================================
RCS file: contrib/tablefunc/sql/tablefunc.sql
diff -N contrib/tablefunc/sql/tablefunc.sql
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- contrib/tablefunc/sql/tablefunc.sql    5 Sep 2002 22:25:35 -0000
***************
*** 0 ****
--- 1,60 ----
+ --
+ -- first, define the functions.  Turn off echoing so that expected file
+ -- does not depend on contents of seg.sql.
+ --
+ \set ECHO none
+ \i tablefunc.sql
+ \set ECHO all
+
+ --
+ -- normal_rand()
+ -- no easy way to do this for regression testing
+ --
+ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FROM CURRENT_TIME(0))::int);
+
+ --
+ -- crosstab()
+ --
+ create table ct(id int, rowclass text, rowid text, attribute text, value text);
+ \copy ct from 'data/ct.data'
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2''
orattribute = ''att3'') order by 1,2;'); 
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;');
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1''
orattribute = ''att2'') order by 1,2;'); 
+
+ select * from crosstab2('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+ select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+ select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' order by 1,2;');
+
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 2) as
c(rowidtext, att1 text, att2 text); 
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 3) as
c(rowidtext, att1 text, att2 text, att3 text); 
+ select * from crosstab('select rowid, attribute, value from ct where rowclass = ''group1'' order by 1,2;', 4) as
c(rowidtext, att1 text, att2 text, att3 text, att4 text); 
+
+ -- test connectby with text based hierarchy
+ CREATE TABLE connectby_text(keyid text, parent_keyid text);
+ \copy connectby_text from 'data/connectby_text.data'
+
+ -- with branch
+ SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid
text,level int, branch text); 
+
+ -- without branch
+ SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text,
levelint); 
+
+ -- test connectby with int based hierarchy
+ CREATE TABLE connectby_int(keyid int, parent_keyid int);
+ \copy connectby_int from 'data/connectby_int.data'
+
+ -- with branch
+ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int,
levelint, branch text); 
+
+ -- without branch
+ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level
int);
+