contrib/tablefunc regression test

Started by Joe Conwayalmost 24 years ago3 messagespatches

mail@joeconway.com

almost 24 years ago

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

Bruce Momjian

bruce@momjian.us

almost 24 years ago

In reply to: Joe Conway (#1)

Re: contrib/tablefunc regression test

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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

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@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

Bruce Momjian

bruce@momjian.us

almost 24 years ago

In reply to: Joe Conway (#1)

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@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

contrib/tablefunc regression test

Attachments: