179
179
-- infinite recursion failure avoided by depth limit
180
180
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);
182
-- should fail as first two columns must have the same type
183
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid text, parent_keyid int, level int, branch text);
185
-- should fail as key field datatype should match return datatype
186
SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text);
188
-- tests for values using custom queries
189
-- query with one column - failed
190
SELECT * FROM connectby('connectby_int', '1; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
191
-- query with two columns first value as NULL
192
SELECT * FROM connectby('connectby_int', 'NULL::int, 1::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
193
-- query with two columns second value as NULL
194
SELECT * FROM connectby('connectby_int', '1::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
195
-- query with two columns, both values as NULL
196
SELECT * FROM connectby('connectby_int', 'NULL::int, NULL::int; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int);
182
198
-- test for falsely detected recursion
183
199
DROP TABLE connectby_int;
184
200
CREATE TABLE connectby_int(keyid int, parent_keyid int);