diff -U3 /Users/admin/pgsql/contrib/tablefunc/expected/tablefunc.out /Users/admin/pgsql/build/testrun/tablefunc/regress/results/tablefunc.out --- /Users/admin/pgsql/contrib/tablefunc/expected/tablefunc.out 2024-08-24 07:29:59 +++ /Users/admin/pgsql/build/testrun/tablefunc/regress/results/tablefunc.out 2024-08-24 07:32:50 @@ -1,162 +1,125 @@ CREATE EXTENSION tablefunc; +ERROR: extension "tablefunc" has no installation script nor update path for version "1.1" -- -- 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) - +ERROR: function normal_rand(integer, integer, numeric) does not exist +LINE 1: SELECT avg(normal_rand)::int, count(*) FROM normal_rand(100,... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- negative number of tuples SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, 250, 0.2); -ERROR: number of rows cannot be negative +ERROR: function normal_rand(integer, integer, numeric) does not exist +LINE 1: SELECT avg(normal_rand)::int, count(*) FROM normal_rand(-1, ... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT count(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_rand_array(10, 3, 1.23::numeric, 8::numeric) as i; - count | avg --------+-------------------- - 10 | 3.0000000000000000 -(1 row) - +ERROR: function normal_rand_array(integer, integer, numeric, numeric) does not exist +LINE 1: ...unt(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_ran... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT count(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_rand_array(10, 3, 1.23::int4, 8::int4) as i; - count | avg --------+-------------------- - 10 | 3.0000000000000000 -(1 row) - +ERROR: function normal_rand_array(integer, integer, integer, integer) does not exist +LINE 1: ...unt(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_ran... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT count(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_rand_array(10, 3, 1.23::int8, 8::int8) as i; - count | avg --------+-------------------- - 10 | 3.0000000000000000 -(1 row) - +ERROR: function normal_rand_array(integer, integer, bigint, bigint) does not exist +LINE 1: ...unt(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_ran... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT count(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_rand_array(10, 3, 1.23::float8, 8::float8) as i; - count | avg --------+-------------------- - 10 | 3.0000000000000000 -(1 row) - +ERROR: function normal_rand_array(integer, integer, double precision, double precision) does not exist +LINE 1: ...unt(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_ran... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT count(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_rand_array(10, 3, 'abc'::text, 'def'::text) as i; -ERROR: unsupported type 25 in normal_rand_array. +ERROR: function normal_rand_array(integer, integer, text, text) does not exist +LINE 1: ...unt(*), avg(COALESCE(array_length(i, 1), 0)) FROM normal_ran... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- 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) - +ERROR: function crosstab2(unknown) does not exist +LINE 1: SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab3(unknown) does not exist +LINE 1: SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab4(unknown) does not exist +LINE 1: SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab2(unknown) does not exist +LINE 1: SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab3(unknown) does not exist +LINE 1: SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab4(unknown) does not exist +LINE 1: SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab2(unknown) does not exist +LINE 1: SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab3(unknown) does not exist +LINE 1: SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab4(unknown) does not exist +LINE 1: SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab2(unknown) does not exist +LINE 1: SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab3(unknown) does not exist +LINE 1: SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab4(unknown) does not exist +LINE 1: SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM c... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. 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) - +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- check it works with OUT parameters, too CREATE FUNCTION crosstab_out(text, OUT rowid text, OUT att1 text, OUT att2 text, OUT att3 text) @@ -174,20 +137,28 @@ -- check error reporting SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') AS ct(row_name text, category_1 text, category_2 text); -ERROR: invalid crosstab source data query -DETAIL: The query must return 3 columns: row_name, category, and value. +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowc... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') AS ct(row_name text); -ERROR: invalid crosstab return type -DETAIL: Return row must have at least two columns. +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') AS ct(row_name int, category_1 text, category_2 text); -ERROR: invalid crosstab return type -DETAIL: Source row_name datatype text does not match return row_name datatype integer. +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') AS ct(row_name text, category_1 text, category_2 int); -ERROR: invalid crosstab return type -DETAIL: Source value datatype text does not match return value datatype integer in column 3. +ERROR: function crosstab(unknown) does not exist +LINE 1: SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- -- hash based crosstab -- @@ -211,106 +182,108 @@ '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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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: crosstab categories query must return at least one row +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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: invalid crosstab categories query -DETAIL: The query must return one column. +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- if category query generates a NULL value, get expected error SELECT * FROM crosstab( 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', 'SELECT NULL::text') AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); -ERROR: crosstab category value must not be null +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- check errors with inappropriate input rowtype SELECT * FROM crosstab( 'SELECT rowid, attribute 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); -ERROR: invalid crosstab source data query -DETAIL: The query must return at least 3 columns: row_name, category, and value. +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM crosstab( 'SELECT rowid, rowdt, 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); -ERROR: invalid crosstab return type -DETAIL: Return row must have 7 columns, not 6. +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- check errors with inappropriate result rowtype SELECT * FROM crosstab( 'SELECT rowid, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1') AS c(rowid text); -ERROR: invalid crosstab return type -DETAIL: Return row must have at least two columns. +ERROR: function crosstab(unknown, unknown) does not exist +LINE 1: SELECT * FROM crosstab( + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- check it works with a named result rowtype create type my_crosstab_result as ( rowid text, rowdt timestamp, @@ -355,157 +328,137 @@ \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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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 +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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 connectby return type -DETAIL: Source key type integer does not match return key type text. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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 connectby return type -DETAIL: Source key type integer does not match return key type double precision. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text); -ERROR: invalid connectby return type -DETAIL: Source parent key type integer does not match return parent key type double precision. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- check other rowtype mismatch cases SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text); -ERROR: invalid connectby return type -DETAIL: Return row must have 3 columns, not 4. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int); -ERROR: invalid connectby return type -DETAIL: Return row must have 4 columns, not 3. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int); -ERROR: invalid connectby return type -DETAIL: Source parent key type integer does not match return parent key type text. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float); -ERROR: invalid connectby return type -DETAIL: Third return column (depth) must be type integer. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float); -ERROR: invalid connectby return type -DETAIL: Fourth return column (branch) must be type text. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text); -ERROR: invalid connectby return type -DETAIL: Fifth return column (serial) must be type integer. +ERROR: function connectby(unknown, unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text); -ERROR: invalid connectby return type -DETAIL: Fourth return column (serial) must be type integer. +ERROR: function connectby(unknown, unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_text', 'keyid', 'parent_k... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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 connectby source data query -DETAIL: The query must return at least two columns. +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', '1; --', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'NULL::int, 1::int;... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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 +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', '1::int, NULL::int;... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'NULL::int, NULL::i... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- test for falsely detected recursion DROP TABLE connectby_int; CREATE TABLE connectby_int(keyid int, parent_keyid int); @@ -515,11 +468,7 @@ 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) - +ERROR: function connectby(unknown, unknown, unknown, unknown, integer, unknown) does not exist +LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts.