-- FUNCTION gfffeatureatts (integer) is a function to get -- data in the same format as the gffatts view so that -- it can be easily converted to GFF attributes. CREATE FUNCTION gfffeatureatts (bigint) RETURNS SETOF gffatts AS ' SELECT feature_id, ''Ontology_term'' AS type, s.name AS attribute FROM cvterm s, feature_cvterm fs WHERE fs.feature_id= $1 AND fs.cvterm_id = s.cvterm_id UNION SELECT feature_id, ''Dbxref'' AS type, d.name || '':'' || s.accession AS attribute FROM dbxref s, feature_dbxref fs, db d WHERE fs.feature_id= $1 AND fs.dbxref_id = s.dbxref_id AND s.db_id = d.db_id UNION SELECT feature_id, ''Alias'' AS type, s.name AS attribute FROM synonym s, feature_synonym fs WHERE fs.feature_id= $1 AND fs.synonym_id = s.synonym_id UNION SELECT fp.feature_id,cv.name,fp.value FROM featureprop fp, cvterm cv WHERE fp.feature_id= $1 AND fp.type_id = cv.cvterm_id UNION SELECT feature_id, ''pub'' AS type, s.series_name || '':'' || s.title AS attribute FROM pub s, feature_pub fs WHERE fs.feature_id= $1 AND fs.pub_id = s.pub_id ' LANGUAGE SQL; -- -- functions for creating coordinate based functions -- -- create a point CREATE OR REPLACE FUNCTION featureslice(bigint, bigint) RETURNS setof featureloc AS 'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)' LANGUAGE 'sql'; --uses the gff3atts to create a GFF3 compliant attribute string CREATE OR REPLACE FUNCTION gffattstring (bigint) RETURNS varchar AS 'DECLARE return_string varchar; f_id ALIAS FOR $1; atts_view gffatts%ROWTYPE; feature_row feature%ROWTYPE; name varchar; uniquename varchar; parent varchar; escape_loc bigint; BEGIN --Get name from feature.name --Get ID from feature.uniquename SELECT INTO feature_row * FROM feature WHERE feature_id = f_id; name = feature_row.name; return_string = ''ID='' || feature_row.uniquename; IF name IS NOT NULL AND name != '''' THEN return_string = return_string ||'';'' || ''Name='' || name; END IF; --Get Parent from feature_relationship SELECT INTO feature_row * FROM feature f, feature_relationship fr WHERE fr.subject_id = f_id AND fr.object_id = f.feature_id; IF FOUND THEN return_string = return_string||'';''||''Parent=''||feature_row.uniquename; END IF; FOR atts_view IN SELECT * FROM gff3atts WHERE feature_id = f_id LOOP escape_loc = position('';'' in atts_view.attribute); IF escape_loc > 0 THEN atts_view.attribute = replace(atts_view.attribute, '';'', ''%3B''); END IF; return_string = return_string || '';'' || atts_view.type || ''='' || atts_view.attribute; END LOOP; RETURN return_string; END; ' LANGUAGE plpgsql; --creates a view that is suitable for creating a GFF3 string --CREATE OR REPLACE VIEW gff3view ( --REMOVED and RECREATED in sequence-gff-views.sql to avoid --using the function above