summaryrefslogtreecommitdiffstats
path: root/contrib/tablefunc/expected
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /contrib/tablefunc/expected
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/tablefunc/expected')
-rw-r--r--contrib/tablefunc/expected/tablefunc.out430
1 files changed, 430 insertions, 0 deletions
diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out
new file mode 100644
index 0000000..464c210
--- /dev/null
+++ b/contrib/tablefunc/expected/tablefunc.out
@@ -0,0 +1,430 @@
+CREATE EXTENSION tablefunc;
+--
+-- normal_rand()
+-- no easy way to do this for regression testing
+--
+SELECT avg(normal_rand)::int, count(*) FROM normal_rand(100, 250, 0.2);
+ avg | count
+-----+-------
+ 250 | 100
+(1 row)
+
+-- negative number of tuples
+SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, 250, 0.2);
+ERROR: number of rows cannot be negative
+--
+-- 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)
+