| 1 |
-- FUNCTION gfffeatureatts (integer) is a function to get |
| 2 |
-- data in the same format as the gffatts view so that |
| 3 |
-- it can be easily converted to GFF attributes. |
| 4 |
|
| 5 |
CREATE FUNCTION gfffeatureatts (bigint) |
| 6 |
RETURNS SETOF gffatts |
| 7 |
AS |
| 8 |
' |
| 9 |
SELECT feature_id, ''Ontology_term'' AS type, s.name AS attribute |
| 10 |
FROM cvterm s, feature_cvterm fs |
| 11 |
WHERE fs.feature_id= $1 AND fs.cvterm_id = s.cvterm_id |
| 12 |
UNION |
| 13 |
SELECT feature_id, ''Dbxref'' AS type, d.name || '':'' || s.accession AS attribute |
| 14 |
FROM dbxref s, feature_dbxref fs, db d |
| 15 |
WHERE fs.feature_id= $1 AND fs.dbxref_id = s.dbxref_id AND s.db_id = d.db_id |
| 16 |
UNION |
| 17 |
SELECT feature_id, ''Alias'' AS type, s.name AS attribute |
| 18 |
FROM synonym s, feature_synonym fs |
| 19 |
WHERE fs.feature_id= $1 AND fs.synonym_id = s.synonym_id |
| 20 |
UNION |
| 21 |
SELECT fp.feature_id,cv.name,fp.value |
| 22 |
FROM featureprop fp, cvterm cv |
| 23 |
WHERE fp.feature_id= $1 AND fp.type_id = cv.cvterm_id |
| 24 |
UNION |
| 25 |
SELECT feature_id, ''pub'' AS type, s.series_name || '':'' || s.title AS attribute |
| 26 |
FROM pub s, feature_pub fs |
| 27 |
WHERE fs.feature_id= $1 AND fs.pub_id = s.pub_id |
| 28 |
' |
| 29 |
LANGUAGE SQL; |
| 30 |
|
| 31 |
|
| 32 |
-- |
| 33 |
-- functions for creating coordinate based functions |
| 34 |
-- |
| 35 |
-- create a point |
| 36 |
CREATE OR REPLACE FUNCTION featureslice(bigint, bigint) RETURNS setof featureloc AS |
| 37 |
'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' |
| 38 |
LANGUAGE 'sql'; |
| 39 |
|
| 40 |
--uses the gff3atts to create a GFF3 compliant attribute string |
| 41 |
CREATE OR REPLACE FUNCTION gffattstring (bigint) RETURNS varchar AS |
| 42 |
'DECLARE |
| 43 |
return_string varchar; |
| 44 |
f_id ALIAS FOR $1; |
| 45 |
atts_view gffatts%ROWTYPE; |
| 46 |
feature_row feature%ROWTYPE; |
| 47 |
name varchar; |
| 48 |
uniquename varchar; |
| 49 |
parent varchar; |
| 50 |
escape_loc bigint; |
| 51 |
BEGIN |
| 52 |
--Get name from feature.name |
| 53 |
--Get ID from feature.uniquename |
| 54 |
|
| 55 |
SELECT INTO feature_row * FROM feature WHERE feature_id = f_id; |
| 56 |
name = feature_row.name; |
| 57 |
return_string = ''ID='' || feature_row.uniquename; |
| 58 |
IF name IS NOT NULL AND name != '''' |
| 59 |
THEN |
| 60 |
return_string = return_string ||'';'' || ''Name='' || name; |
| 61 |
END IF; |
| 62 |
|
| 63 |
--Get Parent from feature_relationship |
| 64 |
SELECT INTO feature_row * FROM feature f, feature_relationship fr |
| 65 |
WHERE fr.subject_id = f_id AND fr.object_id = f.feature_id; |
| 66 |
IF FOUND |
| 67 |
THEN |
| 68 |
return_string = return_string||'';''||''Parent=''||feature_row.uniquename; |
| 69 |
END IF; |
| 70 |
|
| 71 |
FOR atts_view IN SELECT * FROM gff3atts WHERE feature_id = f_id LOOP |
| 72 |
escape_loc = position('';'' in atts_view.attribute); |
| 73 |
IF escape_loc > 0 THEN |
| 74 |
atts_view.attribute = replace(atts_view.attribute, '';'', ''%3B''); |
| 75 |
END IF; |
| 76 |
return_string = return_string || '';'' |
| 77 |
|| atts_view.type || ''='' |
| 78 |
|| atts_view.attribute; |
| 79 |
END LOOP; |
| 80 |
|
| 81 |
RETURN return_string; |
| 82 |
END; |
| 83 |
' |
| 84 |
LANGUAGE plpgsql; |
| 85 |
|
| 86 |
--creates a view that is suitable for creating a GFF3 string |
| 87 |
--CREATE OR REPLACE VIEW gff3view ( |
| 88 |
--REMOVED and RECREATED in sequence-gff-views.sql to avoid |
| 89 |
--using the function above |