---bad precedence: change customed type name ---drop here to remove old function --DROP TYPE feature_by_cvt_type CASCADE; --DROP TYPE fxgsfids_type CASCADE; --DROP TYPE feature_by_fx_type CASCADE; CREATE TYPE feature_by_fx_type AS ( feature_id bigint, depth INT ); CREATE OR REPLACE FUNCTION get_sub_feature_ids(text) RETURNS SETOF feature_by_fx_type AS ' DECLARE sql alias for $1; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN EXECUTE sql LOOP FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_up_feature_ids(text) RETURNS SETOF feature_by_fx_type AS ' DECLARE sql alias for $1; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN EXECUTE sql LOOP FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_feature_ids(text) RETURNS SETOF feature_by_fx_type AS ' DECLARE sql alias for $1; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; myrc3 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN EXECUTE sql LOOP RETURN NEXT myrc; FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc2; END LOOP; FOR myrc3 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc3; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_sub_feature_ids(bigint) RETURNS SETOF feature_by_fx_type AS ' DECLARE root alias for $1; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN SELECT DISTINCT subject_id AS feature_id FROM feature_relationship WHERE object_id = root LOOP RETURN NEXT myrc; FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_up_feature_ids(bigint) RETURNS SETOF feature_by_fx_type AS ' DECLARE leaf alias for $1; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN SELECT DISTINCT object_id AS feature_id FROM feature_relationship WHERE subject_id = leaf LOOP RETURN NEXT myrc; FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_sub_feature_ids(bigint, integer) RETURNS SETOF feature_by_fx_type AS ' DECLARE root alias for $1; depth alias for $2; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN SELECT DISTINCT subject_id AS feature_id, depth FROM feature_relationship WHERE object_id = root LOOP RETURN NEXT myrc; FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id,depth+1) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- depth is reversed and meanless when union with results from get_sub_feature_ids CREATE OR REPLACE FUNCTION get_up_feature_ids(bigint, integer) RETURNS SETOF feature_by_fx_type AS ' DECLARE leaf alias for $1; depth alias for $2; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN FOR myrc IN SELECT DISTINCT object_id AS feature_id, depth FROM feature_relationship WHERE subject_id = leaf LOOP RETURN NEXT myrc; FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id,depth+1) LOOP RETURN NEXT myrc2; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- children feature ids only (not include itself--parent) for SO type and range (src) CREATE OR REPLACE FUNCTION get_sub_feature_ids_by_type_src(cvterm.name%TYPE,feature.uniquename%TYPE,char(1)) RETURNS SETOF feature_by_fx_type AS ' DECLARE gtype alias for $1; src alias for $2; is_an alias for $3; query text; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id) WHERE t.name = '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; IF (STRPOS(gtype, ''%'') > 0) THEN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id) WHERE t.name like '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; END IF; FOR myrc IN SELECT * FROM get_sub_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- by SO type, usefull for tRNA, ncRNA, etc CREATE OR REPLACE FUNCTION get_feature_ids_by_type(cvterm.name%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS ' DECLARE gtype alias for $1; is_an alias for $2; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT f.feature_id FROM feature f, cvterm t WHERE t.cvterm_id = f.type_id AND t.name = '' || quote_literal(gtype) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; IF (STRPOS(gtype, ''%'') > 0) THEN query := ''SELECT DISTINCT f.feature_id FROM feature f, cvterm t WHERE t.cvterm_id = f.type_id AND t.name like '' || quote_literal(gtype) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_feature_ids_by_type_src(cvterm.name%TYPE, feature.uniquename%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS ' DECLARE gtype alias for $1; src alias for $2; is_an alias for $3; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id) WHERE t.name = '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; IF (STRPOS(gtype, ''%'') > 0) THEN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id) WHERE t.name like '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_feature_ids_by_type_name(cvterm.name%TYPE, feature.uniquename%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS ' DECLARE gtype alias for $1; name alias for $2; is_an alias for $3; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) WHERE t.name = '' || quote_literal(gtype) || '' AND (f.uniquename = '' || quote_literal(name) || '' OR f.name = '' || quote_literal(name) || '') AND f.is_analysis = '' || quote_literal(is_an) || '';''; IF (STRPOS(name, ''%'') > 0) THEN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) WHERE t.name = '' || quote_literal(gtype) || '' AND (f.uniquename like '' || quote_literal(name) || '' OR f.name like '' || quote_literal(name) || '') AND f.is_analysis = '' || quote_literal(is_an) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- get all feature ids (including children) for feature that has an ontology term (say GO function) CREATE OR REPLACE FUNCTION get_feature_ids_by_ont(cv.name%TYPE,cvterm.name%TYPE) RETURNS SETOF feature_by_fx_type AS ' DECLARE aspect alias for $1; term alias for $2; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT fcvt.feature_id FROM feature_cvterm fcvt, cv, cvterm t WHERE cv.cv_id = t.cv_id AND t.cvterm_id = fcvt.cvterm_id AND cv.name = '' || quote_literal(aspect) || '' AND t.name = '' || quote_literal(term) || '';''; IF (STRPOS(term, ''%'') > 0) THEN query := ''SELECT DISTINCT fcvt.feature_id FROM feature_cvterm fcvt, cv, cvterm t WHERE cv.cv_id = t.cv_id AND t.cvterm_id = fcvt.cvterm_id AND cv.name = '' || quote_literal(aspect) || '' AND t.name like '' || quote_literal(term) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION get_feature_ids_by_ont_root(cv.name%TYPE,cvterm.name%TYPE) RETURNS SETOF feature_by_fx_type AS ' DECLARE aspect alias for $1; term alias for $2; query TEXT; subquery TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN subquery := ''SELECT t.cvterm_id FROM cv, cvterm t WHERE cv.cv_id = t.cv_id AND cv.name = '' || quote_literal(aspect) || '' AND t.name = '' || quote_literal(term) || '';''; IF (STRPOS(term, ''%'') > 0) THEN subquery := ''SELECT t.cvterm_id FROM cv, cvterm t WHERE cv.cv_id = t.cv_id AND cv.name = '' || quote_literal(aspect) || '' AND t.name like '' || quote_literal(term) || '';''; END IF; query := ''SELECT DISTINCT fcvt.feature_id FROM feature_cvterm fcvt INNER JOIN (SELECT cvterm_id FROM get_it_sub_cvterm_ids('' || quote_literal(subquery) || '')) AS ont ON (fcvt.cvterm_id = ont.cvterm_id);''; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- get all feature ids (including children) for feature with the property (type, val) CREATE OR REPLACE FUNCTION get_feature_ids_by_property(cvterm.name%TYPE,varchar) RETURNS SETOF feature_by_fx_type AS ' DECLARE p_type alias for $1; p_val alias for $2; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT fprop.feature_id FROM featureprop fprop, cvterm t WHERE t.cvterm_id = fprop.type_id AND t.name = '' || quote_literal(p_type) || '' AND fprop.value = '' || quote_literal(p_val) || '';''; IF (STRPOS(p_val, ''%'') > 0) THEN query := ''SELECT DISTINCT fprop.feature_id FROM featureprop fprop, cvterm t WHERE t.cvterm_id = fprop.type_id AND t.name = '' || quote_literal(p_type) || '' AND fprop.value like '' || quote_literal(p_val) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; --- get all feature ids (including children) for feature with the property val CREATE OR REPLACE FUNCTION get_feature_ids_by_propval(varchar) RETURNS SETOF feature_by_fx_type AS ' DECLARE p_val alias for $1; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type%ROWTYPE; BEGIN query := ''SELECT DISTINCT fprop.feature_id FROM featureprop fprop WHERE fprop.value = '' || quote_literal(p_val) || '';''; IF (STRPOS(p_val, ''%'') > 0) THEN query := ''SELECT DISTINCT fprop.feature_id FROM featureprop fprop WHERE fprop.value like '' || quote_literal(p_val) || '';''; END IF; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ---4 args: ptype, ctype, count, operator (valid SQL number comparison operator), and is_analysis ---get feature ids for any node with type = ptype whose child node type = ctype ---and child node feature count comparing (using operator) to ccount CREATE OR REPLACE FUNCTION get_feature_ids_by_child_count(cvterm.name%TYPE, cvterm.name%TYPE, INTEGER, varchar, char(1)) RETURNS SETOF feature_by_fx_type AS ' DECLARE ptype alias for $1; ctype alias for $2; ccount alias for $3; operator alias for $4; is_an alias for $5; query TEXT; myrc feature_by_fx_type%ROWTYPE; myrc2 feature_by_fx_type %ROWTYPE; BEGIN query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join (select count(*) as c, p.feature_id FROM feature p INNER join cvterm pt ON (p.type_id = pt.cvterm_id) INNER join feature_relationship fr ON (p.feature_id = fr.object_id) INNER join feature c ON (c.feature_id = fr.subject_id) INNER join cvterm ct ON (c.type_id = ct.cvterm_id) WHERE pt.name = '' || quote_literal(ptype) || '' AND ct.name = '' || quote_literal(ctype) || '' AND p.is_analysis = '' || quote_literal(is_an) || '' group by p.feature_id) as cq ON (cq.feature_id = f.feature_id) WHERE cq.c '' || operator || ccount || '';''; ---RAISE NOTICE ''%'', query; FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP RETURN NEXT myrc; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql';