| 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'; |