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