diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/tablefunc/expected/tablefunc.out | 427 |
1 files changed, 427 insertions, 0 deletions
diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out new file mode 100644 index 0000000..fffadc6 --- /dev/null +++ b/contrib/tablefunc/expected/tablefunc.out @@ -0,0 +1,427 @@ +CREATE EXTENSION tablefunc; +-- +-- normal_rand() +-- 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) + |