contrib/tablefunc/expected/tablefunc.out

   1 --
   2 -- first, define the functions.  Turn off echoing so that expected file
   3 -- does not depend on contents of tablefunc.sql.
   4 --
   5 SET client_min_messages = warning;
   6 \set ECHO none
   7 RESET client_min_messages;
   8 --
   9 -- normal_rand()
  10 -- no easy way to do this for regression testing
  11 --
  12 SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2);
  13  avg
  14 -----
  15  250
  16 (1 row)
  17
  18 --
  19 -- crosstab()
  20 --
  21 CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
  22 \copy ct from 'data/ct.data'
  23 SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
  24  row_name | category_1 | category_2
  25 ----------+------------+------------
  26  test1    | val2       | val3
  27  test2    | val6       | val7
  28           | val10      | val11
  29 (3 rows)
  30
  31 SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
  32  row_name | category_1 | category_2 | category_3
  33 ----------+------------+------------+------------
  34  test1    | val2       | val3       |
  35  test2    | val6       | val7       |
  36           | val10      | val11      |
  37 (3 rows)
  38
  39 SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
  40  row_name | category_1 | category_2 | category_3 | category_4
  41 ----------+------------+------------+------------+------------
  42  test1    | val2       | val3       |            |
  43  test2    | val6       | val7       |            |
  44           | val10      | val11      |            |
  45 (3 rows)
  46
  47 SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
  48  row_name | category_1 | category_2
  49 ----------+------------+------------
  50  test1    | val1       | val2
  51  test2    | val5       | val6
  52           | val9       | val10
  53 (3 rows)
  54
  55 SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
  56  row_name | category_1 | category_2 | category_3
  57 ----------+------------+------------+------------
  58  test1    | val1       | val2       | val3
  59  test2    | val5       | val6       | val7
  60           | val9       | val10      | val11
  61 (3 rows)
  62
  63 SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
  64  row_name | category_1 | category_2 | category_3 | category_4
  65 ----------+------------+------------+------------+------------
  66  test1    | val1       | val2       | val3       | val4
  67  test2    | val5       | val6       | val7       | val8
  68           | val9       | val10      | val11      | val12
  69 (3 rows)
  70
  71 SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
  72  row_name | category_1 | category_2
  73 ----------+------------+------------
  74  test3    | val1       | val2
  75  test4    | val4       | val5
  76 (2 rows)
  77
  78 SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
  79  row_name | category_1 | category_2 | category_3
  80 ----------+------------+------------+------------
  81  test3    | val1       | val2       |
  82  test4    | val4       | val5       |
  83 (2 rows)
  84
  85 SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
  86  row_name | category_1 | category_2 | category_3 | category_4
  87 ----------+------------+------------+------------+------------
  88  test3    | val1       | val2       |            |
  89  test4    | val4       | val5       |            |
  90 (2 rows)
  91
  92 SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
  93  row_name | category_1 | category_2
  94 ----------+------------+------------
  95  test3    | val1       | val2
  96  test4    | val4       | val5
  97 (2 rows)
  98
  99 SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 100  row_name | category_1 | category_2 | category_3
 101 ----------+------------+------------+------------
 102  test3    | val1       | val2       | val3
 103  test4    | val4       | val5       | val6
 104 (2 rows)
 105
 106 SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
 107  row_name | category_1 | category_2 | category_3 | category_4
 108 ----------+------------+------------+------------+------------
 109  test3    | val1       | val2       | val3       |
 110  test4    | val4       | val5       | val6       |
 111 (2 rows)
 112
 113 SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text);
 114  rowid | att1 | att2
 115 -------+------+-------
 116  test1 | val1 | val2
 117  test2 | val5 | val6
 118        | val9 | val10
 119 (3 rows)
 120
 121 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);
 122  rowid | att1 | att2  | att3
 123 -------+------+-------+-------
 124  test1 | val1 | val2  | val3
 125  test2 | val5 | val6  | val7
 126        | val9 | val10 | val11
 127 (3 rows)
 128
 129 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);
 130  rowid | att1 | att2  | att3  | att4
 131 -------+------+-------+-------+-------
 132  test1 | val1 | val2  | val3  | val4
 133  test2 | val5 | val6  | val7  | val8
 134        | val9 | val10 | val11 | val12
 135 (3 rows)
 136
 137 -- check it works with OUT parameters, too
 138 CREATE FUNCTION crosstab_out(text,
 139         OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text)
 140 RETURNS setof record
 141 AS '$libdir/tablefunc','crosstab'
 142 LANGUAGE C STABLE STRICT;
 143 SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
 144  rowid | att1 | att2  | att3
 145 -------+------+-------+-------
 146  test1 | val1 | val2  | val3
 147  test2 | val5 | val6  | val7
 148        | val9 | val10 | val11
 149 (3 rows)
 150
 151 --
 152 -- hash based crosstab
 153 --
 154 create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
 155 NOTICE:  CREATE TABLE will create implicit sequence "cth_id_seq" for serial column "cth.id"
 156 insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
 157 insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
 158 -- the next line is intentionally left commented and is therefore a "missing" attribute
 159 -- insert into cth values(DEFAULT,'test1','01 March 2003','test_startdate','28 February 2003');
 160 insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
 161 insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
 162 insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
 163 insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
 164 insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
 165 -- next group tests for NULL rowids
 166 insert into cth values(DEFAULT,NULL,'25 October 2007','temperature','57');
 167 insert into cth values(DEFAULT,NULL,'25 October 2007','test_result','PASS');
 168 insert into cth values(DEFAULT,NULL,'25 October 2007','test_startdate','24 October 2007');
 169 insert into cth values(DEFAULT,NULL,'25 October 2007','volts','1.41234');
 170 -- return attributes as plain text
 171 SELECT * FROM crosstab(
 172   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 173   'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 174 AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 175  rowid |          rowdt           | temperature | test_result | test_startdate  |  volts
 176 -------+--------------------------+-------------+-------------+-----------------+---------
 177  test1 | Sat Mar 01 00:00:00 2003 | 42          | PASS        |                 | 2.6987
 178  test2 | Sun Mar 02 00:00:00 2003 | 53          | FAIL        | 01 March 2003   | 3.1234
 179        | Thu Oct 25 00:00:00 2007 | 57          | PASS        | 24 October 2007 | 1.41234
 180 (3 rows)
 181
 182 -- this time without rowdt
 183 SELECT * FROM crosstab(
 184   'SELECT rowid, attribute, val FROM cth ORDER BY 1',
 185   'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 186 AS c(rowid text, temperature text, test_result text, test_startdate text, volts text);
 187  rowid | temperature | test_result | test_startdate  |  volts
 188 -------+-------------+-------------+-----------------+---------
 189  test1 | 42          | PASS        |                 | 2.6987
 190  test2 | 53          | FAIL        | 01 March 2003   | 3.1234
 191        | 57          | PASS        | 24 October 2007 | 1.41234
 192 (3 rows)
 193
 194 -- convert attributes to specific datatypes
 195 SELECT * FROM crosstab(
 196   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 197   'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 198 AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 199  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
 200 -------+--------------------------+-------------+-------------+--------------------------+---------
 201  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
 202  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
 203        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
 204 (3 rows)
 205
 206 -- source query and category query out of sync
 207 SELECT * FROM crosstab(
 208   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 209   'SELECT DISTINCT attribute FROM cth WHERE attribute IN (''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
 210 AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp);
 211  rowid |          rowdt           | temperature | test_result |      test_startdate
 212 -------+--------------------------+-------------+-------------+--------------------------
 213  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |
 214  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003
 215        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007
 216 (3 rows)
 217
 218 -- if category query generates no rows, get expected error
 219 SELECT * FROM crosstab(
 220   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 221   'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
 222 AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 223 ERROR:  provided "categories" SQL must return 1 column of at least one row
 224 -- if category query generates more than one column, get expected error
 225 SELECT * FROM crosstab(
 226   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 227   'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
 228 AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
 229 ERROR:  provided "categories" SQL must return 1 column of at least one row
 230 -- if source query returns zero rows, get zero rows returned
 231 SELECT * FROM crosstab(
 232   'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
 233   'SELECT DISTINCT attribute FROM cth ORDER BY 1')
 234 AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 235  rowid | rowdt | temperature | test_result | test_startdate | volts
 236 -------+-------+-------------+-------------+----------------+-------
 237 (0 rows)
 238
 239 -- if source query returns zero rows, get zero rows returned even if category query generates no rows
 240 SELECT * FROM crosstab(
 241   'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
 242   'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
 243 AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
 244  rowid | rowdt | temperature | test_result | test_startdate | volts
 245 -------+-------+-------------+-------------+----------------+-------
 246 (0 rows)
 247
 248 -- check it works with a named result rowtype
 249 create type my_crosstab_result as (
 250   rowid text, rowdt timestamp,
 251   temperature int4, test_result text, test_startdate timestamp, volts float8);
 252 CREATE FUNCTION crosstab_named(text, text)
 253 RETURNS setof my_crosstab_result
 254 AS '$libdir/tablefunc','crosstab_hash'
 255 LANGUAGE C STABLE STRICT;
 256 SELECT * FROM crosstab_named(
 257   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 258   'SELECT DISTINCT attribute FROM cth ORDER BY 1');
 259  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
 260 -------+--------------------------+-------------+-------------+--------------------------+---------
 261  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
 262  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
 263        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
 264 (3 rows)
 265
 266 -- check it works with OUT parameters
 267 CREATE FUNCTION crosstab_out(text, text,
 268   OUT rowid text, OUT rowdt timestamp,
 269   OUT temperature int4, OUT test_result text,
 270   OUT test_startdate timestamp, OUT volts float8)
 271 RETURNS setof record
 272 AS '$libdir/tablefunc','crosstab_hash'
 273 LANGUAGE C STABLE STRICT;
 274 SELECT * FROM crosstab_out(
 275   'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
 276   'SELECT DISTINCT attribute FROM cth ORDER BY 1');
 277  rowid |          rowdt           | temperature | test_result |      test_startdate      |  volts
 278 -------+--------------------------+-------------+-------------+--------------------------+---------
 279  test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          |  2.6987
 280  test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 |  3.1234
 281        | Thu Oct 25 00:00:00 2007 |          57 | PASS        | Wed Oct 24 00:00:00 2007 | 1.41234
 282 (3 rows)
 283
 284 --
 285 -- connectby
 286 --
 287 -- test connectby with text based hierarchy
 288 CREATE TABLE connectby_text(keyid text, parent_keyid text, pos int);
 289 \copy connectby_text from 'data/connectby_text.data'
 290 -- with branch, without orderby
 291 SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text);
 292  keyid | parent_keyid | level |       branch
 293 -------+--------------+-------+---------------------
 294  row2  |              |     0 | row2
 295  row4  | row2         |     1 | row2~row4
 296  row6  | row4         |     2 | row2~row4~row6
 297  row8  | row6         |     3 | row2~row4~row6~row8
 298  row5  | row2         |     1 | row2~row5
 299  row9  | row5         |     2 | row2~row5~row9
 300 (6 rows)
 301
 302 -- without branch, without orderby
 303 SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'row2', 0) AS t(keyid text, parent_keyid text, level int);
 304  keyid | parent_keyid | level
 305 -------+--------------+-------
 306  row2  |              |     0
 307  row4  | row2         |     1
 308  row6  | row4         |     2
 309  row8  | row6         |     3
 310  row5  | row2         |     1
 311  row9  | row5         |     2
 312 (6 rows)
 313
 314 -- with branch, with orderby
 315 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;
 316  keyid | parent_keyid | level |       branch        | pos
 317 -------+--------------+-------+---------------------+-----
 318  row2  |              |     0 | row2                |   1
 319  row5  | row2         |     1 | row2~row5           |   2
 320  row9  | row5         |     2 | row2~row5~row9      |   3
 321  row4  | row2         |     1 | row2~row4           |   4
 322  row6  | row4         |     2 | row2~row4~row6      |   5
 323  row8  | row6         |     3 | row2~row4~row6~row8 |   6
 324 (6 rows)
 325
 326 -- without branch, with orderby
 327 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;
 328  keyid | parent_keyid | level | pos
 329 -------+--------------+-------+-----
 330  row2  |              |     0 |   1
 331  row5  | row2         |     1 |   2
 332  row9  | row5         |     2 |   3
 333  row4  | row2         |     1 |   4
 334  row6  | row4         |     2 |   5
 335  row8  | row6         |     3 |   6
 336 (6 rows)
 337
 338 -- test connectby with int based hierarchy
 339 CREATE TABLE connectby_int(keyid int, parent_keyid int);
 340 \copy connectby_int from 'data/connectby_int.data'
 341 -- with branch
 342 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 343  keyid | parent_keyid | level | branch
 344 -------+--------------+-------+---------
 345      2 |              |     0 | 2
 346      4 |            2 |     1 | 2~4
 347      6 |            4 |     2 | 2~4~6
 348      8 |            6 |     3 | 2~4~6~8
 349      5 |            2 |     1 | 2~5
 350      9 |            5 |     2 | 2~5~9
 351 (6 rows)
 352
 353 -- without branch
 354 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
 355  keyid | parent_keyid | level
 356 -------+--------------+-------
 357      2 |              |     0
 358      4 |            2 |     1
 359      6 |            4 |     2
 360      8 |            6 |     3
 361      5 |            2 |     1
 362      9 |            5 |     2
 363 (6 rows)
 364
 365 -- recursion detection
 366 INSERT INTO connectby_int VALUES(10,9);
 367 INSERT INTO connectby_int VALUES(11,10);
 368 INSERT INTO connectby_int VALUES(9,11);
 369 -- should fail due to infinite recursion
 370 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 371 ERROR:  infinite recursion detected
 372 -- infinite recursion failure avoided by depth limit
 373 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);
 374  keyid | parent_keyid | level |   branch
 375 -------+--------------+-------+-------------
 376      2 |              |     0 | 2
 377      4 |            2 |     1 | 2~4
 378      6 |            4 |     2 | 2~4~6
 379      8 |            6 |     3 | 2~4~6~8
 380      5 |            2 |     1 | 2~5
 381      9 |            5 |     2 | 2~5~9
 382     10 |            9 |     3 | 2~5~9~10
 383     11 |           10 |     4 | 2~5~9~10~11
 384 (8 rows)
 385
 386 -- test for falsely detected recursion
 387 DROP TABLE connectby_int;
 388 CREATE TABLE connectby_int(keyid int, parent_keyid int);
 389 INSERT INTO connectby_int VALUES(11,NULL);
 390 INSERT INTO connectby_int VALUES(10,11);
 391 INSERT INTO connectby_int VALUES(111,11);
 392 INSERT INTO connectby_int VALUES(1,111);
 393 -- this should not fail due to recursion detection
 394 SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);
 395  keyid | parent_keyid | level |  branch
 396 -------+--------------+-------+----------
 397     11 |              |     0 | 11
 398     10 |           11 |     1 | 11-10
 399    111 |           11 |     1 | 11-111
 400      1 |          111 |     2 | 11-111-1
 401 (4 rows)
 402