1CREATE EXTENSION tablefunc; 2 3-- 4-- normal_rand() 5-- no easy way to do this for regression testing 6-- 7SELECT avg(normal_rand)::int, count(*) FROM normal_rand(100, 250, 0.2); 8-- negative number of tuples 9SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, 250, 0.2); 10 11-- 12-- crosstab() 13-- 14CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text); 15\copy ct from 'data/ct.data' 16 17SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;'); 18SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;'); 19SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;'); 20 21SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); 22SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); 23SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); 24 25SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;'); 26SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;'); 27SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;'); 28 29SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); 30SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); 31SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); 32 33SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text); 34SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text); 35SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text, att4 text); 36 37-- check it works with OUT parameters, too 38 39CREATE FUNCTION crosstab_out(text, 40 OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text) 41RETURNS setof record 42AS '$libdir/tablefunc','crosstab' 43LANGUAGE C STABLE STRICT; 44 45SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); 46 47-- 48-- hash based crosstab 49-- 50create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); 51insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42'); 52insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS'); 53-- the next line is intentionally left commented and is therefore a "missing" attribute 54-- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003'); 55insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987'); 56insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53'); 57insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL'); 58insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003'); 59insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234'); 60-- next group tests for NULL rowids 61insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57'); 62insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS'); 63insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007'); 64insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234'); 65 66-- return attributes as plain text 67SELECT * FROM crosstab( 68 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 69 'SELECT DISTINCT attribute FROM cth ORDER BY 1') 70AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); 71 72-- this time without rowdt 73SELECT * FROM crosstab( 74 'SELECT rowid, attribute, val FROM cth ORDER BY 1', 75 'SELECT DISTINCT attribute FROM cth ORDER BY 1') 76AS c(rowid text, temperature text, test_result text, test_startdate text, volts text); 77 78-- convert attributes to specific datatypes 79SELECT * FROM crosstab( 80 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 81 'SELECT DISTINCT attribute FROM cth ORDER BY 1') 82AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); 83 84-- source query and category query out of sync 85SELECT * FROM crosstab( 86 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 87 'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1') 88AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp); 89 90-- if category query generates no rows, get expected error 91SELECT * FROM crosstab( 92 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 93 'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1') 94AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); 95 96-- if category query generates more than one column, get expected error 97SELECT * FROM crosstab( 98 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 99 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') 100AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); 101 102-- if source query returns zero rows, get zero rows returned 103SELECT * FROM crosstab( 104 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', 105 'SELECT DISTINCT attribute FROM cth ORDER BY 1') 106AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); 107 108-- if source query returns zero rows, get zero rows returned even if category query generates no rows 109SELECT * FROM crosstab( 110 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', 111 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') 112AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); 113 114-- check it works with a named result rowtype 115 116create type my_crosstab_result as ( 117 rowid text, rowdt timestamp, 118 temperature int4, test_result text, test_startdate timestamp, volts float8); 119 120CREATE FUNCTION crosstab_named(text, text) 121RETURNS setof my_crosstab_result 122AS '$libdir/tablefunc','crosstab_hash' 123LANGUAGE C STABLE STRICT; 124 125SELECT * FROM crosstab_named( 126 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 127 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); 128 129-- check it works with OUT parameters 130 131CREATE FUNCTION crosstab_out(text, text, 132 OUT rowid text, OUT rowdt timestamp, 133 OUT temperature int4, OUT test_result text, 134 OUT test_startdate timestamp, OUT volts float8) 135RETURNS setof record 136AS '$libdir/tablefunc','crosstab_hash' 137LANGUAGE C STABLE STRICT; 138 139SELECT * FROM crosstab_out( 140 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 141 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); 142 143-- 144-- connectby 145-- 146 147-- test connectby with text based hierarchy 148CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int); 149\copy connectby_text from 'data/connectby_text.data' 150 151-- with branch, without orderby 152SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text); 153 154-- without branch, without orderby 155SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int); 156 157-- with branch, with orderby 158SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos int) ORDER BY t.pos; 159 160-- without branch, with orderby 161SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos int) ORDER BY t.pos; 162 163-- test connectby with int based hierarchy 164CREATE TABLE connectby_int(keyid int, parent_keyid int); 165\copy connectby_int from 'data/connectby_int.data' 166 167-- with branch 168SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text); 169 170-- without branch 171SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); 172 173-- recursion detection 174INSERT INTO connectby_int VALUES(10,9); 175INSERT INTO connectby_int VALUES(11,10); 176INSERT INTO connectby_int VALUES(9,11); 177 178-- should fail due to infinite recursion 179SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text); 180 181-- infinite recursion failure avoided by depth limit 182SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text); 183 184-- should fail as first two columns must have the same type 185SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid text, parent_keyid int, level int, branch text); 186 187-- should fail as key field datatype should match return datatype 188SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text); 189 190-- tests for values using custom queries 191-- query with one column - failed 192SELECT * FROM connectby('connectby_int', '1; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); 193-- query with two columns first value as NULL 194SELECT * FROM connectby('connectby_int', 'NULL::int, 1::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); 195-- query with two columns second value as NULL 196SELECT * FROM connectby('connectby_int', '1::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); 197-- query with two columns, both values as NULL 198SELECT * FROM connectby('connectby_int', 'NULL::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); 199 200-- test for falsely detected recursion 201DROP TABLE connectby_int; 202CREATE TABLE connectby_int(keyid int, parent_keyid int); 203INSERT INTO connectby_int VALUES(11,NULL); 204INSERT INTO connectby_int VALUES(10,11); 205INSERT INTO connectby_int VALUES(111,11); 206INSERT INTO connectby_int VALUES(1,111); 207-- this should not fail due to recursion detection 208SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text); 209