CREATE OR REPLACE FUNCTION _get_all_subject_ids(bigint) 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(bigint) 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(bigint) 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(bigint) 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(bigint) 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(bigint) 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';