tablefunc.out

PostgreSQL7.4.6 for Linux

-------+--------------------------+-------------+-------------+--------------------------+--------
 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
(2 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
(2 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)

--
-- 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)

-- 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)