*** /home/vpopov/Projects/pwdtest.new/postgresql/contrib/tablefunc/expected/tablefunc.out 2016-03-15 14:32:12.945978099 +0300 --- /home/vpopov/Projects/pwdtest.new/postgresql/contrib/tablefunc/results/tablefunc.out 2016-03-15 16:58:19.297568781 +0300 *************** *** 4,427 **** -- no easy way to do this for regression testing -- SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2); ! avg ! ----- ! 250 ! (1 row) ! ! -- ! -- crosstab() ! -- ! CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text); ! \copy ct from 'data/ct.data' ! SELECT * FROM crosstab2('SELECT rowid, attribute, val 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 ! | val10 | val11 ! (3 rows) ! ! SELECT * FROM crosstab3('SELECT rowid, attribute, val 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 | ! | val10 | val11 | ! (3 rows) ! ! SELECT * FROM crosstab4('SELECT rowid, attribute, val 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 | | ! | val10 | val11 | | ! (3 rows) ! ! SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); ! row_name | category_1 | category_2 ! ----------+------------+------------ ! test1 | val1 | val2 ! test2 | val5 | val6 ! | val9 | val10 ! (3 rows) ! ! SELECT * FROM crosstab3('SELECT rowid, attribute, val 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 ! | val9 | val10 | val11 ! (3 rows) ! ! SELECT * FROM crosstab4('SELECT rowid, attribute, val 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 ! | val9 | val10 | val11 | val12 ! (3 rows) ! ! SELECT * FROM crosstab2('SELECT rowid, attribute, val 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, val 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, val 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, val 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, val 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, val 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, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text); ! rowid | att1 | att2 ! -------+------+------- ! test1 | val1 | val2 ! test2 | val5 | val6 ! | val9 | val10 ! (3 rows) ! ! SELECT * 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); ! rowid | att1 | att2 | att3 ! -------+------+-------+------- ! test1 | val1 | val2 | val3 ! test2 | val5 | val6 | val7 ! | val9 | val10 | val11 ! (3 rows) ! ! SELECT * 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); ! rowid | att1 | att2 | att3 | att4 ! -------+------+-------+-------+------- ! test1 | val1 | val2 | val3 | val4 ! test2 | val5 | val6 | val7 | val8 ! | val9 | val10 | val11 | val12 ! (3 rows) ! ! -- check it works with OUT parameters, too ! CREATE FUNCTION crosstab_out(text, ! OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text) ! RETURNS setof record ! AS '$libdir/tablefunc','crosstab' ! LANGUAGE C STABLE STRICT; ! SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); ! rowid | att1 | att2 | att3 ! -------+------+-------+------- ! test1 | val1 | val2 | val3 ! test2 | val5 | val6 | val7 ! | val9 | val10 | val11 ! (3 rows) ! ! -- ! -- hash based crosstab ! -- ! create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text); ! insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42'); ! insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS'); ! -- the next line is intentionally left commented and is therefore a "missing" attribute ! -- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003'); ! insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987'); ! insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53'); ! insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL'); ! insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003'); ! insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234'); ! -- next group tests for NULL rowids ! insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57'); ! insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS'); ! insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007'); ! insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234'); ! -- return attributes as plain text ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+--------------------------+-------------+-------------+-----------------+--------- ! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 ! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | 01 March 2003 | 3.1234 ! | Thu Oct 25 00:00:00 2007 | 57 | PASS | 24 October 2007 | 1.41234 ! (3 rows) ! ! -- this time without rowdt ! SELECT * FROM crosstab( ! 'SELECT rowid, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1') ! AS c(rowid text, temperature text, test_result text, test_startdate text, volts text); ! rowid | temperature | test_result | test_startdate | volts ! -------+-------------+-------------+-----------------+--------- ! test1 | 42 | PASS | | 2.6987 ! test2 | 53 | FAIL | 01 March 2003 | 3.1234 ! | 57 | PASS | 24 October 2007 | 1.41234 ! (3 rows) ! ! -- convert attributes to specific datatypes ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+--------------------------+-------------+-------------+--------------------------+--------- ! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 ! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 ! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234 ! (3 rows) ! ! -- source query and category query out of sync ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp); ! rowid | rowdt | temperature | test_result | test_startdate ! -------+--------------------------+-------------+-------------+-------------------------- ! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | ! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 ! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 ! (3 rows) ! ! -- if category query generates no rows, get expected error ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); ! ERROR: provided "categories" SQL must return 1 column of at least one row ! -- if category query generates more than one column, get expected error ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') ! AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); ! ERROR: provided "categories" SQL must return 1 column of at least one row ! -- if source query returns zero rows, get zero rows returned ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+-------+-------------+-------------+----------------+------- ! (0 rows) ! ! -- if source query returns zero rows, get zero rows returned even if category query generates no rows ! SELECT * FROM crosstab( ! 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') ! AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+-------+-------------+-------------+----------------+------- ! (0 rows) ! ! -- check it works with a named result rowtype ! create type my_crosstab_result as ( ! rowid text, rowdt timestamp, ! temperature int4, test_result text, test_startdate timestamp, volts float8); ! CREATE FUNCTION crosstab_named(text, text) ! RETURNS setof my_crosstab_result ! AS '$libdir/tablefunc','crosstab_hash' ! LANGUAGE C STABLE STRICT; ! SELECT * FROM crosstab_named( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+--------------------------+-------------+-------------+--------------------------+--------- ! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 ! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 ! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234 ! (3 rows) ! ! -- check it works with OUT parameters ! CREATE FUNCTION crosstab_out(text, text, ! OUT rowid text, OUT rowdt timestamp, ! OUT temperature int4, OUT test_result text, ! OUT test_startdate timestamp, OUT volts float8) ! RETURNS setof record ! AS '$libdir/tablefunc','crosstab_hash' ! LANGUAGE C STABLE STRICT; ! SELECT * FROM crosstab_out( ! 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', ! 'SELECT DISTINCT attribute FROM cth ORDER BY 1'); ! rowid | rowdt | temperature | test_result | test_startdate | volts ! -------+--------------------------+-------------+-------------+--------------------------+--------- ! test1 | Sat Mar 01 00:00:00 2003 | 42 | PASS | | 2.6987 ! test2 | Sun Mar 02 00:00:00 2003 | 53 | FAIL | Sat Mar 01 00:00:00 2003 | 3.1234 ! | Thu Oct 25 00:00:00 2007 | 57 | PASS | Wed Oct 24 00:00:00 2007 | 1.41234 ! (3 rows) ! ! -- ! -- connectby ! -- ! -- test connectby with text based hierarchy ! CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int); ! \copy connectby_text from 'data/connectby_text.data' ! -- with branch, without orderby ! 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, without orderby ! 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) ! ! -- with branch, with orderby ! SELECT * 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; ! keyid | parent_keyid | level | branch | pos ! -------+--------------+-------+---------------------+----- ! row2 | | 0 | row2 | 1 ! row5 | row2 | 1 | row2~row5 | 2 ! row9 | row5 | 2 | row2~row5~row9 | 3 ! row4 | row2 | 1 | row2~row4 | 4 ! row6 | row4 | 2 | row2~row4~row6 | 5 ! row8 | row6 | 3 | row2~row4~row6~row8 | 6 ! (6 rows) ! ! -- without branch, with orderby ! SELECT * 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; ! keyid | parent_keyid | level | pos ! -------+--------------+-------+----- ! row2 | | 0 | 1 ! row5 | row2 | 1 | 2 ! row9 | row5 | 2 | 3 ! row4 | row2 | 1 | 4 ! row6 | row4 | 2 | 5 ! row8 | row6 | 3 | 6 ! (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) ! ! -- recursion detection ! INSERT INTO connectby_int VALUES(10,9); ! INSERT INTO connectby_int VALUES(11,10); ! INSERT INTO connectby_int VALUES(9,11); ! -- should fail due to infinite recursion ! SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text); ! ERROR: infinite recursion detected ! -- infinite recursion failure avoided by depth limit ! SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') 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 ! 10 | 9 | 3 | 2~5~9~10 ! 11 | 10 | 4 | 2~5~9~10~11 ! (8 rows) ! ! -- should fail as first two columns must have the same type ! SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid text, parent_keyid int, level int, branch text); ! ERROR: invalid return type ! DETAIL: First two columns must be the same type. ! -- should fail as key field datatype should match return datatype ! SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text); ! ERROR: invalid return type ! DETAIL: SQL key field type double precision does not match return key field type integer. ! -- tests for values using custom queries ! -- query with one column - failed ! SELECT * FROM connectby('connectby_int', '1; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); ! ERROR: invalid return type ! DETAIL: Query must return at least two columns. ! -- query with two columns first value as NULL ! SELECT * FROM connectby('connectby_int', 'NULL::int, 1::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); ! keyid | parent_keyid | level ! -------+--------------+------- ! 2 | | 0 ! | 1 | 1 ! (2 rows) ! ! -- query with two columns second value as NULL ! SELECT * FROM connectby('connectby_int', '1::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); ! ERROR: infinite recursion detected ! -- query with two columns, both values as NULL ! SELECT * FROM connectby('connectby_int', 'NULL::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); ! keyid | parent_keyid | level ! -------+--------------+------- ! 2 | | 0 ! | | 1 ! (2 rows) ! ! -- test for falsely detected recursion ! DROP TABLE connectby_int; ! CREATE TABLE connectby_int(keyid int, parent_keyid int); ! INSERT INTO connectby_int VALUES(11,NULL); ! INSERT INTO connectby_int VALUES(10,11); ! INSERT INTO connectby_int VALUES(111,11); ! INSERT INTO connectby_int VALUES(1,111); ! -- this should not fail due to recursion detection ! SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text); ! keyid | parent_keyid | level | branch ! -------+--------------+-------+---------- ! 11 | | 0 | 11 ! 10 | 11 | 1 | 11-10 ! 111 | 11 | 1 | 11-111 ! 1 | 111 | 2 | 11-111-1 ! (4 rows) ! --- 4,10 ---- -- no easy way to do this for regression testing -- SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2); ! server closed the connection unexpectedly ! This probably means the server terminated abnormally ! before or while processing the request. ! connection to server was lost ======================================================================