Debian Codegraph @

modules/sequence/gff-bridge/sequence-gff-funcs.plpgsql

raw
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