tablefunc.diffs
text/plain
Filename: tablefunc.diffs
Type: text/plain
Part: 12
*** /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
======================================================================