-- DROP FUNCTION schema_xyz.next_uid_pg(int8);

CREATE OR REPLACE FUNCTION schema_xyz.next_uid_pg(my_uid bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$

DECLARE

ergebnis bigint;
max_level bigint;
descent_cnt bigint;

/*

If the uid does not appear as a parent in descent (in such a way that there is only a single successor, number of 'sibling predecessors' does not matter' -- > you can either do this via type in descent or simply count successors in the table) it simply remains as it is. If there is a successor, the search continues until there is no more and then the last one is output.

But use the TYPE field!!! There are cases with type 2 where only one of the successors is entered, i.e. by counting lines you get type 1.

top --> bottom SELECT * FROM connectby('relation', 'uid_1', 'uid_0', '1803',0) AS t(uid_1 numeric, uid_0 numeric, level int); 
*/





BEGIN

select count(*) into descent_cnt 
from schema_xyz.descent_1_3
where 
parent=my_uid;

if descent_cnt=1 then 

SELECT max(l1) into max_level
from
(SELECT parent, successor, level as l1 
FROM connectby('schema_xyz.descent_1_3', 'successor', 'parent', my_uid::varchar, 0)
AS t(successor bigint, parent bigint, level int)) as xxx;

SELECT successor into ergebnis
from
(SELECT parent, successor, level as l1 
FROM connectby('schema_xyz.descent_1_3', 'successor', 'parent', my_uid::varchar, 0)
AS t(successor bigint, parent bigint, level int)) as xxx
where l1=max_level;

else ergebnis:=my_uid;

end if;

RETURN ergebnis;
end;

$function$
;