Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 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$
;