Debian Codegraph @

modules/cv/functions/fill_cvtermpath.plpgsql

raw
1 --- example: select * from fill_cvtermpath(7); where 7 is cv_id for an ontology
2 --- fill path from the node to its children and their children
3 CREATE OR REPLACE FUNCTION _fill_cvtermpath4node(BIGINT, BIGINT, BIGINT, BIGINT, INTEGER) RETURNS INTEGER AS
4 '
5 DECLARE
6 origin alias for $1;
7 child_id alias for $2;
8 cvid alias for $3;
9 typeid alias for $4;
10 depth alias for $5;
11 cterm cvterm_relationship%ROWTYPE;
12 exist_c int;
13
14 BEGIN
15
16 --- RAISE NOTICE ''depth=% root=%'', depth,child_id;
17 --- not check type_id as it may be null and not very meaningful in cvtermpath when pathdistance > 1
18 SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth;
19
20 IF (exist_c = 0) THEN
21 INSERT INTO cvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES(origin, child_id, cvid, typeid, depth);
22 END IF;
23 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP
24 PERFORM _fill_cvtermpath4node(origin, cterm.subject_id, cvid, cterm.type_id, depth+1);
25 END LOOP;
26 RETURN 1;
27 END;
28 '
29 LANGUAGE 'plpgsql';
30
31
32 CREATE OR REPLACE FUNCTION _fill_cvtermpath4root(BIGINT, BIGINT) RETURNS INTEGER AS
33 '
34 DECLARE
35 rootid alias for $1;
36 cvid alias for $2;
37 ttype bigint;
38 cterm cvterm_relationship%ROWTYPE;
39 child cvterm_relationship%ROWTYPE;
40
41 BEGIN
42
43 SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a'');
44 PERFORM _fill_cvtermpath4node(rootid, rootid, cvid, ttype, 0);
45 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP
46 PERFORM _fill_cvtermpath4root(cterm.subject_id, cvid);
47 -- RAISE NOTICE ''DONE for term, %'', cterm.subject_id;
48 END LOOP;
49 RETURN 1;
50 END;
51 '
52 LANGUAGE 'plpgsql';
53
54 CREATE OR REPLACE FUNCTION fill_cvtermpath(BIGINT) RETURNS INTEGER AS
55 '
56 DECLARE
57 cvid alias for $1;
58 root cvterm%ROWTYPE;
59
60 BEGIN
61
62 DELETE FROM cvtermpath WHERE cv_id = cvid;
63
64 FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP
65 PERFORM _fill_cvtermpath4root(root.cvterm_id, root.cv_id);
66 END LOOP;
67 RETURN 1;
68 END;
69 '
70 LANGUAGE 'plpgsql';
71
72 CREATE OR REPLACE FUNCTION fill_cvtermpath(cv.name%TYPE) RETURNS INTEGER AS
73 '
74 DECLARE
75 cvname alias for $1;
76 cv_id bigint;
77 rtn int;
78 BEGIN
79
80 SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname;
81 SELECT INTO rtn fill_cvtermpath(cv_id);
82 RETURN rtn;
83 END;
84 '
85 LANGUAGE 'plpgsql';