diff -U3 /tmp/cirrus-ci-build/contrib/tablefunc/expected/tablefunc.out /tmp/cirrus-ci-build/build/testrun/tablefunc/regress/results/tablefunc.out --- /tmp/cirrus-ci-build/contrib/tablefunc/expected/tablefunc.out 2024-10-25 07:03:29.660213673 +0000 +++ /tmp/cirrus-ci-build/build/testrun/tablefunc/regress/results/tablefunc.out 2024-10-25 07:08:24.179455509 +0000 @@ -239,261 +239,7 @@ '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: invalid crosstab categories query -DETAIL: The query must return one column. --- if category query generates a NULL value, get expected error -SELECT * FROM crosstab( - 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', - 'SELECT NULL::text') -AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); -ERROR: crosstab category value must not be null --- 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 errors with inappropriate input rowtype -SELECT * FROM crosstab( - 'SELECT rowid, attribute 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); -ERROR: invalid crosstab source data query -DETAIL: The query must return at least 3 columns: row_name, category, and value. -SELECT * FROM crosstab( - 'SELECT rowid, rowdt, 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); -ERROR: invalid crosstab return type -DETAIL: Return row must have 7 columns, not 6. --- check errors with inappropriate result rowtype -SELECT * FROM crosstab( - 'SELECT rowid, attribute, val FROM cth ORDER BY 1', - 'SELECT DISTINCT attribute FROM cth ORDER BY 1') -AS c(rowid text); -ERROR: invalid crosstab return type -DETAIL: Return row must have at least two columns. --- 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 connectby return type -DETAIL: Source key type integer does not match return key type text. --- 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 connectby return type -DETAIL: Source key type integer does not match return key type double precision. -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text); -ERROR: invalid connectby return type -DETAIL: Source parent key type integer does not match return parent key type double precision. --- check other rowtype mismatch cases -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text); -ERROR: invalid connectby return type -DETAIL: Return row must have 3 columns, not 4. -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int); -ERROR: invalid connectby return type -DETAIL: Return row must have 4 columns, not 3. -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int); -ERROR: invalid connectby return type -DETAIL: Source parent key type integer does not match return parent key type text. -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float); -ERROR: invalid connectby return type -DETAIL: Third return column (depth) must be type integer. -SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float); -ERROR: invalid connectby return type -DETAIL: Fourth return column (branch) must be type text. -SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text); -ERROR: invalid connectby return type -DETAIL: Fifth return column (serial) must be type integer. -SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text); -ERROR: invalid connectby return type -DETAIL: Fourth return column (serial) must be 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 connectby source data query -DETAIL: The 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) - +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +connection to server was lost