Re: contrib/tablefunc regression test - Mailing list pgsql-patches

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(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''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(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''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(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);
> +

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

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