Debian Codegraph @

modules/cv/functions/get_subj_obj_ids.plpgsql

raw
1
2 CREATE OR REPLACE FUNCTION _get_all_subject_ids(bigint) RETURNS SETOF cvtermpath AS
3 '
4 DECLARE
5 root alias for $1;
6 cterm cvtermpath%ROWTYPE;
7 cterm2 cvtermpath%ROWTYPE;
8 BEGIN
9
10 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP
11 RETURN NEXT cterm;
12 FOR cterm2 IN SELECT * FROM _get_all_subject_ids(cterm.subject_id) LOOP
13 RETURN NEXT cterm2;
14 END LOOP;
15 END LOOP;
16 RETURN;
17 END;
18 '
19 LANGUAGE 'plpgsql';
20
21 ---arg: parent term id
22 ---return: all children term id and their parent term id with relationship type id
23 CREATE OR REPLACE FUNCTION get_all_subject_ids(bigint) RETURNS SETOF cvtermpath AS
24 '
25 DECLARE
26 root alias for $1;
27 cterm cvtermpath%ROWTYPE;
28 exist_c int;
29 BEGIN
30
31 SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = root and pathdistance <= 0;
32 IF (exist_c > 0) THEN
33 FOR cterm IN SELECT * FROM cvtermpath WHERE object_id = root and pathdistance > 0 LOOP
34 RETURN NEXT cterm;
35 END LOOP;
36 ELSE
37 FOR cterm IN SELECT * FROM _get_all_subject_ids(root) LOOP
38 RETURN NEXT cterm;
39 END LOOP;
40 END IF;
41 RETURN;
42 END;
43 '
44 LANGUAGE 'plpgsql';
45
46 CREATE OR REPLACE FUNCTION get_graph_below(bigint) RETURNS SETOF cvtermpath AS
47 '
48 DECLARE
49 root alias for $1;
50 cterm cvtermpath%ROWTYPE;
51 cterm2 cvtermpath%ROWTYPE;
52
53 BEGIN
54
55 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP
56 RETURN NEXT cterm;
57 FOR cterm2 IN SELECT * FROM get_all_subject_ids(cterm.subject_id) LOOP
58 RETURN NEXT cterm2;
59 END LOOP;
60 END LOOP;
61 RETURN;
62 END;
63 '
64 LANGUAGE 'plpgsql';
65
66
67 CREATE OR REPLACE FUNCTION get_graph_above(bigint) RETURNS SETOF cvtermpath AS
68 '
69 DECLARE
70 leaf alias for $1;
71 cterm cvtermpath%ROWTYPE;
72 cterm2 cvtermpath%ROWTYPE;
73
74 BEGIN
75
76 FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP
77 RETURN NEXT cterm;
78 FOR cterm2 IN SELECT * FROM get_all_object_ids(cterm.object_id) LOOP
79 RETURN NEXT cterm2;
80 END LOOP;
81 END LOOP;
82 RETURN;
83 END;
84 '
85 LANGUAGE 'plpgsql';
86
87 CREATE OR REPLACE FUNCTION _get_all_object_ids(bigint) RETURNS SETOF cvtermpath AS
88 '
89 DECLARE
90 leaf alias for $1;
91 cterm cvtermpath%ROWTYPE;
92 cterm2 cvtermpath%ROWTYPE;
93 BEGIN
94
95 FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP
96 RETURN NEXT cterm;
97 FOR cterm2 IN SELECT * FROM _get_all_object_ids(cterm.object_id) LOOP
98 RETURN NEXT cterm2;
99 END LOOP;
100 END LOOP;
101 RETURN;
102 END;
103 '
104 LANGUAGE 'plpgsql';
105
106 ---arg: child term id
107 ---return: all parent term id and their childrent term id with relationship type id
108 CREATE OR REPLACE FUNCTION get_all_object_ids(bigint) RETURNS SETOF cvtermpath AS
109 '
110 DECLARE
111 leaf alias for $1;
112 cterm cvtermpath%ROWTYPE;
113 exist_c int;
114 BEGIN
115
116
117 SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = leaf and pathdistance <= 0;
118 IF (exist_c > 0) THEN
119 FOR cterm IN SELECT * FROM cvtermpath WHERE subject_id = leaf AND pathdistance > 0 LOOP
120 RETURN NEXT cterm;
121 END LOOP;
122 ELSE
123 FOR cterm IN SELECT * FROM _get_all_object_ids(leaf) LOOP
124 RETURN NEXT cterm;
125 END LOOP;
126 END IF;
127 RETURN;
128 END;
129 '
130 LANGUAGE 'plpgsql';
131
132 ---arg: sql statement which must be in the form of select cvterm_id from ...
133 ---return: a set of cvterm ids that includes what is in sql statement and their children (subject ids)
134 CREATE OR REPLACE FUNCTION get_it_sub_cvterm_ids(text) RETURNS SETOF cvterm AS
135 '
136 DECLARE
137 query alias for $1;
138 cterm cvterm%ROWTYPE;
139 cterm2 cvterm%ROWTYPE;
140 BEGIN
141 FOR cterm IN EXECUTE query LOOP
142 RETURN NEXT cterm;
143 FOR cterm2 IN SELECT subject_id as cvterm_id FROM get_all_subject_ids(cterm.cvterm_id) LOOP
144 RETURN NEXT cterm2;
145 END LOOP;
146 END LOOP;
147 RETURN;
148 END;
149 '
150 LANGUAGE 'plpgsql';