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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
CREATE OR REPLACE FUNCTION _get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
root alias for $1;
cterm cvtermpath%ROWTYPE;
cterm2 cvtermpath%ROWTYPE;
BEGIN
FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP
RETURN NEXT cterm;
FOR cterm2 IN SELECT * FROM _get_all_subject_ids(cterm.subject_id) LOOP
RETURN NEXT cterm2;
END LOOP;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
---arg: parent term id
---return: all children term id and their parent term id with relationship type id
CREATE OR REPLACE FUNCTION get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
root alias for $1;
cterm cvtermpath%ROWTYPE;
exist_c int;
BEGIN
SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = root and pathdistance <= 0;
IF (exist_c > 0) THEN
FOR cterm IN SELECT * FROM cvtermpath WHERE object_id = root and pathdistance > 0 LOOP
RETURN NEXT cterm;
END LOOP;
ELSE
FOR cterm IN SELECT * FROM _get_all_subject_ids(root) LOOP
RETURN NEXT cterm;
END LOOP;
END IF;
RETURN;
END;
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_graph_below(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
root alias for $1;
cterm cvtermpath%ROWTYPE;
cterm2 cvtermpath%ROWTYPE;
BEGIN
FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP
RETURN NEXT cterm;
FOR cterm2 IN SELECT * FROM get_all_subject_ids(cterm.subject_id) LOOP
RETURN NEXT cterm2;
END LOOP;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_graph_above(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
leaf alias for $1;
cterm cvtermpath%ROWTYPE;
cterm2 cvtermpath%ROWTYPE;
BEGIN
FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP
RETURN NEXT cterm;
FOR cterm2 IN SELECT * FROM get_all_object_ids(cterm.object_id) LOOP
RETURN NEXT cterm2;
END LOOP;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION _get_all_object_ids(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
leaf alias for $1;
cterm cvtermpath%ROWTYPE;
cterm2 cvtermpath%ROWTYPE;
BEGIN
FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP
RETURN NEXT cterm;
FOR cterm2 IN SELECT * FROM _get_all_object_ids(cterm.object_id) LOOP
RETURN NEXT cterm2;
END LOOP;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
---arg: child term id
---return: all parent term id and their childrent term id with relationship type id
CREATE OR REPLACE FUNCTION get_all_object_ids(integer) RETURNS SETOF cvtermpath AS
'
DECLARE
leaf alias for $1;
cterm cvtermpath%ROWTYPE;
exist_c int;
BEGIN
SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = leaf and pathdistance <= 0;
IF (exist_c > 0) THEN
FOR cterm IN SELECT * FROM cvtermpath WHERE subject_id = leaf AND pathdistance > 0 LOOP
RETURN NEXT cterm;
END LOOP;
ELSE
FOR cterm IN SELECT * FROM _get_all_object_ids(leaf) LOOP
RETURN NEXT cterm;
END LOOP;
END IF;
RETURN;
END;
'
LANGUAGE 'plpgsql';
---arg: sql statement which must be in the form of select cvterm_id from ...
---return: a set of cvterm ids that includes what is in sql statement and their children (subject ids)
CREATE OR REPLACE FUNCTION get_it_sub_cvterm_ids(text) RETURNS SETOF cvterm AS
'
DECLARE
query alias for $1;
cterm cvterm%ROWTYPE;
cterm2 cvterm%ROWTYPE;
BEGIN
FOR cterm IN EXECUTE query LOOP
RETURN NEXT cterm;
FOR cterm2 IN SELECT subject_id as cvterm_id FROM get_all_subject_ids(cterm.cvterm_id) LOOP
RETURN NEXT cterm2;
END LOOP;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
|
