Re: contrib/tablefunc regression test

Patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> 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'' or attribute = ''att3'') order by 1,2;');
> - select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> - select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''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'' or attribute = ''att2'') order by 1,2;');
> - select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> - select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''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(rowid text, att1 text, att2 text);
> - 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);
> - 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);
> -
> - -- 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, level int);
> -
> - -- 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, level int, 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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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'' or attribute = ''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(rowid text, 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(rowid text, 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(rowid text, 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, level int);
> + 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, level int, 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'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''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'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab3('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') order by 1,2;');
> + select * from crosstab4('select rowid, attribute, value from ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''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(rowid text, att1 text, att2 text);
> + 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);
> + 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);
> +
> + -- 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, level int);
> +
> + -- 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, level int, branch text);
> +
> + -- without branch
> + SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
> +

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073