-- these are nearly identical to the triggers that flybase uses, but will -- serve as the foundation of general triggers for GMOD. Things that need to -- be done: -- --* indentify the items that have to be dynamically (at make time) extrapolated. --* allow a more flexible naming scheme --* remove the portion creating a flybase dbxref. --* probably 20 more things I haven't thought of yet. --gets the next available uniquename; note that it is --destructive because it calls nextval on a sequence CREATE OR REPLACE FUNCTION next_uniquename() RETURNS varchar AS ' DECLARE prename varchar; f_uniquename varchar; prefix varchar; suffix varchar; id varchar; maxid bigint; BEGIN SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''prefix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''suffix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO maxid nextval(''uniquename_id_generator''); RAISE NOTICE ''maxid is:%'', maxid; id:=lpad(maxid, 6, ''000000''); f_uniquename:=CAST(prefix||id||suffix as VARCHAR); RETURN f_uniquename; END; 'LANGUAGE plpgsql; DROP TRIGGER tr_feature_del ON feature; CREATE OR REPLACE function fn_feature_del() RETURNS TRIGGER AS ' DECLARE f_type cvterm.name%TYPE; f_id_gene feature.feature_id%TYPE; f_id_transcript feature.feature_id%TYPE; f_id_exon feature.feature_id%TYPE; f_id_exon_temp feature.feature_id%TYPE; f_id_protein feature.feature_id%TYPE; f_id_allele feature.feature_id%TYPE; fr_object_id feature.feature_id%TYPE; f_type_gene CONSTANT varchar :=''gene''; f_type_exon CONSTANT varchar :=''exon''; f_type_transcript CONSTANT varchar :=''mRNA''; f_type_snoRNA CONSTANT varchar :=''snoRNA''; f_type_ncRNA CONSTANT varchar :=''ncRNA''; f_type_snRNA CONSTANT varchar :=''snRNA''; f_type_tRNA CONSTANT varchar :=''tRNA''; f_type_rRNA CONSTANT varchar :=''rRNA''; f_type_promoter CONSTANT varchar :=''promoter''; f_type_repeat_region CONSTANT varchar :=''repeat_region''; f_type_miRNA CONSTANT varchar :=''miRNA''; f_type_transposable_element CONSTANT varchar :=''transposable_element''; f_type_pseudo CONSTANT varchar :=''pseudogene''; f_type_protein CONSTANT varchar :=''polypeptide''; f_type_allele CONSTANT varchar :=''alleleof''; f_return feature.feature_id%TYPE; f_row feature%ROWTYPE; fr_row_transcript feature_relationship%ROWTYPE; fr_row_exon feature_relationship%ROWTYPE; fr_row_protein feature_relationship%ROWTYPE; message varchar(255); BEGIN RAISE NOTICE ''enter f_d, feature uniquename:%, type_id:%'',OLD.uniquename, OLD.type_id; f_return:=OLD.feature_id; SELECT INTO f_type c.name from feature f, cvterm c where f.feature_id=OLD.feature_id and f.type_id=c.cvterm_id; IF f_type=f_type_gene THEN SELECT INTO f_id_allele fr.subject_id from feature_relationship fr, cvterm c where (fr.object_id=OLD.feature_id or fr.subject_id=OLD.feature_id) and fr.type_id=c.cvterm_id and c.name=f_type_allele; IF NOT FOUND THEN FOR fr_row_transcript IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP SELECT INTO f_id_transcript f.feature_id from feature f, cvterm c where f.feature_id=fr_row_transcript.subject_id and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region); SELECT INTO f_id_gene f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_transcript and f.type_id=c.cvterm_id and c.name=f_type_gene and f.feature_id !=OLD.feature_id; IF f_id_gene IS NULL and f_id_transcript IS NOT NULL THEN RAISE NOTICE ''delete lonely transcript:%'', f_id_transcript; message:=CAST(''delete lonely transcript''||f_id_transcript AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_transcript); delete from feature where feature_id=f_id_transcript; ELSIF f_id_gene IS NOT NULL AND F_id_transcript IS NOT NULL THEN RAISE NOTICE ''There is another gene:% associated with this transcript:%, so this transcript will be kept'',f_id_gene, f_id_transcript; message:=CAST(''There is another gene:''||f_id_gene||'' associated with this transcript:''||f_id_transcript AS TEXT); END IF; END LOOP; message:=CAST(''delete gene:''||OLD.feature_id AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', OLD.feature_id); ELSE RAISE NOTICE ''there is other allele associated with this gene:%'', f_id_allele; message:=CAST(''There is other allele associated with this gene:''||f_id_allele AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_transcript); return NULL; END IF; ELSIF (f_type=f_type_transcript or f_type=f_type_ncRNA or f_type=f_type_snoRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_rRNA or f_type=f_type_pseudo or f_type=f_type_miRNA or f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region) THEN FOR fr_row_exon IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP select INTO f_id_exon f.feature_id from feature f, cvterm c where f.feature_id=fr_row_exon.subject_id and f.type_id=c.cvterm_id and c.name=f_type_exon; SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_exon and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region) and f.feature_id!=OLD.feature_id; IF f_id_transcript IS NULL and f_id_exon IS NOT NULL THEN RAISE NOTICE ''delete lonely exon:%'', f_id_exon; delete from feature where feature_id=f_id_exon; message:=CAST(''delete lonely exon:''||f_id_exon AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_exon); ELSIF f_id_transcript IS NOT NULL and f_id_exon IS NOT NULL THEN RAISE NOTICE ''There is another transcript:% associated with this exon:%, so this exon will be kept'', f_id_transcript, f_id_exon; message:=CAST(''There is another transcript:''||f_id_transcript||'' associated with this exon:''||f_id_exon AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_exon); END IF; END LOOP; FOR fr_row_protein IN SELECT * from feature_relationship fr where fr.object_id=OLD.feature_id LOOP SELECT INTO f_id_protein f.feature_id from feature f, cvterm c where f.feature_id=fr_row_protein.subject_id and f.type_id=c.cvterm_id and c.name=f_type_protein; SELECT INTO f_id_transcript f.feature_id from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=f_id_protein and f.type_id=c.cvterm_id and (c.name=f_type_transcript or c.name=f_type_ncRNA or c.name=f_type_snoRNA or c.name=f_type_snRNA or c.name=f_type_tRNA or c.name=f_type_rRNA or c.name=f_type_pseudo or c.name=f_type_miRNA or c.name=f_type_transposable_element or c.name=f_type_promoter or c.name=f_type_repeat_region) and f.feature_id !=OLD.feature_id; IF f_id_transcript IS NULL and f_id_protein IS NOT NULL THEN RAISE NOTICE ''delete lonely polypeptide:%'', f_id_protein; delete from feature where feature_id=f_id_protein; message:=CAST(''delete lonely polypeptide:''||f_id_protein AS TEXT); insert into trigger_log(value, table_name, id) values(message, ''feature'', f_id_protein); ELSIF f_id_transcript IS NOT NULL and f_id_protein IS NOT NULL THEN RAISE NOTICE ''There is another transcript:% associated with this polypeptide:%, so this exon will be kept'', f_id_transcript, f_id_protein; END IF; END LOOP; END IF; RAISE NOTICE ''leave f_d ....''; RETURN OLD; END; 'LANGUAGE 'plpgsql'; GRANT ALL ON FUNCTION fn_feature_del() TO PUBLIC; CREATE TRIGGER tr_feature_del BEFORE DELETE ON feature for EACH ROW EXECUTE PROCEDURE fn_feature_del(); DROP TRIGGER feature_assignname_tr_i ON feature; CREATE OR REPLACE FUNCTION feature_assignname_fn_i() RETURNS TRIGGER AS ' DECLARE maxid bigint; id varchar(255); max_id bigint; is_anal feature.is_analysis%TYPE; prefix cvtermprop.value%TYPE; suffix cvtermprop.value%TYPE; f_row_g feature%ROWTYPE; f_row_e feature%ROWTYPE; f_row_t feature%ROWTYPE; f_row_p feature%ROWTYPE; f_type cvterm.name%TYPE; f_type_id cvterm.cvterm_id%TYPE; letter_t varchar; letter_p varchar; f_uniquename_temp feature.uniquename%TYPE; f_uniquename feature.uniquename%TYPE; f_uniquename_tr feature.uniquename%TYPE; f_uniquename_exon feature.uniquename%TYPE; f_uniquename_protein feature.uniquename%TYPE; f_name feature.name%TYPE; s_type_id synonym.type_id%TYPE; s_id synonym.synonym_id%TYPE; c_cv_id cv.cv_id%TYPE; f_s_id feature_synonym.feature_synonym_id%TYPE; fr_row feature_relationship%ROWTYPE; f_type_gene CONSTANT varchar :=''gene''; f_type_exon CONSTANT varchar :=''exon''; f_type_transcript CONSTANT varchar :=''mRNA''; f_type_snoRNA CONSTANT varchar :=''snoRNA''; f_type_ncRNA CONSTANT varchar :=''ncRNA''; f_type_snRNA CONSTANT varchar :=''snRNA''; f_type_tRNA CONSTANT varchar :=''tRNA''; f_type_rRNA CONSTANT varchar :=''rRNA''; f_type_promoter CONSTANT varchar :=''promoter''; f_type_repeat_region CONSTANT varchar :=''repeat_region''; f_type_miRNA CONSTANT varchar :=''miRNA''; f_type_transposable_element CONSTANT varchar :=''transposable_element''; f_type_pseudo CONSTANT varchar :=''pseudogene''; f_type_protein CONSTANT varchar :=''polypeptide''; f_type_allele CONSTANT varchar :=''alleleof''; f_type_remark CONSTANT varchar :=''remark''; f_dbname_gadfly CONSTANT varchar :=''DB:GR''; f_dbname_FB CONSTANT varchar :=''null''; o_genus CONSTANT varchar :=''Oryza''; o_species CONSTANT varchar:=''sativa''; c_name_synonym CONSTANT varchar:=''synonym''; cv_cvname_synonym CONSTANT varchar:=''null''; p_miniref CONSTANT varchar:=''none''; p_id pub.pub_id%TYPE; BEGIN SELECT INTO is_anal is_analysis FROM feature WHERE uniquename = NEW.uniquename and type_id = NEW.type_id and organism_id = NEW.organism_id; IF (is_anal) THEN RETURN NEW; END IF; SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''prefix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''suffix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.uniquename=NEW.uniquename and f.organism_id =NEW.organism_id; SELECT INTO p_id pub_id from pub where uniquename = p_miniref; SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id; RAISE NOTICE ''assigning names, prefix:%, suffix:%, type:%, current uniquename:%'',prefix,suffix,f_type,NEW.uniquename; IF (NEW.uniquename like prefix||''%:temp%''||suffix OR NEW.uniquename like prefix||''%-temp%'') THEN SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.uniquename=NEW.uniquename and f.organism_id =NEW.organism_id; --SELECT INTO p_id pub_id from pub where uniquename = p_miniref; --SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id; SELECT INTO f_uniquename * FROM next_uniquename(); SELECT INTO f_row_g * from feature where uniquename=NEW.uniquename and organism_id=NEW.organism_id; IF f_type = f_type_gene THEN IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN f_name = f_uniquename; UPDATE feature set uniquename=f_uniquename, name=f_uniquename where feature_id=f_row_g.feature_id; ELSE f_name = f_row_g.name; UPDATE feature set uniquename=f_uniquename where feature_id=f_row_g.feature_id; END IF; ELSIF (f_type=f_type_transcript or f_type=f_type_ncRNA or f_type=f_type_snoRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_rRNA or f_type=f_type_pseudo or f_type=f_type_miRNA or f_type=f_type_protein or f_type=f_type_exon) THEN IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN f_name = null; ELSE f_name = f_row_g.name; END IF; UPDATE feature set uniquename=f_uniquename,name=f_name where feature_id=f_row_g.feature_id; ELSIF ( f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region or f_type=f_type_remark ) THEN IF NEW.name like ''%temp%'' or NEW.name IS NULL THEN f_name := CAST(f_uniquename||''-''||f_type AS TEXT); UPDATE feature set uniquename=f_uniquename, name=f_uniquename where feature_id=f_row_g.feature_id; ELSE f_name = f_row_g.name; UPDATE feature set uniquename=f_uniquename, name=f_name where feature_id=f_row_g.feature_id; END IF; END IF; RAISE NOTICE ''new uniquename of this feature is:%'', f_uniquename; --insert into synonym, feature_synonym SELECT INTO s_id synonym_id from synonym where name=f_uniquename and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(f_uniquename, f_uniquename, s_type_id); SELECT INTO s_id synonym_id from synonym where name=f_uniquename and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_g.feature_id and synonym_id=s_id and pub_id=p_id; IF f_s_id IS NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_g.feature_id, s_id, p_id, ''true''); END IF; RAISE NOTICE ''feature_id:%, synonym_id:% for uniquename'', f_row_g.feature_id, s_id; IF f_name IS NOT NULL THEN SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_g.feature_id and synonym_id=s_id and pub_id=p_id; IF f_s_id IS NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_g.feature_id, s_id, p_id, ''true''); END IF; RAISE NOTICE ''feature_id:%, synonym_id:% for name'', f_row_g.feature_id, s_id; END IF; END IF; --ends if uniquename like temp return NEW; END; 'LANGUAGE plpgsql; GRANT ALL ON FUNCTION feature_assignname_fn_i() TO PUBLIC; CREATE TRIGGER feature_assignname_tr_i AFTER INSERT ON feature for EACH ROW EXECUTE PROCEDURE feature_assignname_fn_i(); DROP TRIGGER feature_relationship_tr_d ON feature_relationship; CREATE OR REPLACE FUNCTION feature_relationship_fn_d() RETURNS TRIGGER AS ' DECLARE maxid bigint; id varchar(255); loginfo varchar(255); len bigint; f_row_g feature%ROWTYPE; f_row_e feature%ROWTYPE; f_row_t feature%ROWTYPE; f_row_p feature%ROWTYPE; f_type cvterm.name%TYPE; f_type_temp cvterm.name%TYPE; letter_e varchar(100); letter_t varchar(100); letter_p varchar(100); f_uniquename_gene feature.uniquename%TYPE; f_uniquename_transcript feature.uniquename%TYPE; f_uniquename_exon feature.uniquename%TYPE; f_uniquename_protein feature.uniquename%TYPE; f_d_id feature_dbxref.feature_dbxref_id%TYPE; d_id dbxref.dbxref_id%TYPE; s_type_id synonym.type_id%TYPE; s_id synonym.synonym_id%TYPE; p_id pub.pub_id%TYPE; fr_row feature_relationship%ROWTYPE; f_accession_temp varchar(255); f_accession varchar(255); f_type_gene CONSTANT varchar :=''gene''; f_type_exon CONSTANT varchar :=''exon''; f_type_transcript CONSTANT varchar :=''mRNA''; f_type_snoRNA CONSTANT varchar :=''snoRNA''; f_type_ncRNA CONSTANT varchar :=''ncRNA''; f_type_snRNA CONSTANT varchar :=''snRNA''; f_type_tRNA CONSTANT varchar :=''tRNA''; f_type_rRNA CONSTANT varchar :=''rRNA''; f_type_promoter CONSTANT varchar :=''promoter''; f_type_repeat_region CONSTANT varchar :=''repeat_region''; f_type_miRNA CONSTANT varchar :=''miRNA''; f_type_transposable_element CONSTANT varchar :=''transposable_element''; f_type_pseudo CONSTANT varchar :=''pseudogene''; f_type_protein CONSTANT varchar :=''polypeptide''; f_type_allele CONSTANT varchar :=''alleleof''; f_dbname_gadfly CONSTANT varchar :=''Gadfly''; f_dbname_FB CONSTANT varchar :=''FlyBase''; c_name_synonym CONSTANT varchar:=''synonym''; cv_cvname_synonym CONSTANT varchar:=''synonym type''; p_miniref CONSTANT varchar:=''none''; BEGIN RAISE NOTICE ''enter fr_d, fr.object_id:%, fr.subject_id:%'', OLD.object_id, OLD.subject_id; SELECT INTO f_type name from cvterm where cvterm_id=OLD.type_id; IF f_type=f_type_allele THEN RAISE NOTICE ''delete relationship beteen gene:% and allele:%'', OLD.object_id, OLD.subject_id; ELSE SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.feature_id=OLD.object_id; IF f_type=f_type_gene THEN SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=OLD.subject_id and f.type_id=c.cvterm_id; IF (f_type_temp=f_type_transcript or f_type_temp=f_type_ncRNA or f_type_temp=f_type_snoRNA or f_type_temp=f_type_snRNA or f_type_temp=f_type_tRNA or f_type_temp=f_type_rRNA or f_type_temp=f_type_miRNA or f_type_temp=f_type_pseudo or f_type_temp=f_type_transposable_element or f_type_temp=f_type_promoter or f_type_temp=f_type_repeat_region ) THEN SELECT INTO fr_row * from feature_relationship where object_id<>OLD.object_id and subject_id=OLD.subject_id; IF fr_row.object_id IS NULL THEN RAISE NOTICE ''delete this lonely transcript:%'', OLD.subject_id; delete from feature where feature_id=OLD.subject_id; END IF; ELSE RAISE NOTICE ''wrong feature_relationship: gene->NO_transcript:object_id:%, subject_id:%'', OLD.object_id, OLD.subject_id; END IF; ELSIF (f_type=f_type_transcript or f_type=f_type_snoRNA or f_type=f_type_ncRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_miRNA or f_type=f_type_rRNA or f_type=f_type_pseudo or f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region) THEN SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=OLD.subject_id and f.type_id=c.cvterm_id; IF f_type_temp=f_type_protein or f_type_temp=f_type_exon THEN SELECT INTO fr_row * from feature_relationship where subject_id=OLD.subject_id and object_id<>OLD.object_id; IF fr_row.object_id IS NULL THEN RAISE NOTICE ''delete this lonely exon/polypeptide:%'', OLD.subject_id; delete from feature where feature_id=OLD.subject_id; END IF; ELSE RAISE NOTICE ''wrong relationship: transcript->NO_polypeptide/exon: objfeature:%, subjfeature:%'',OLD.object_id, OLD.subject_id; END IF; END IF; END IF; RAISE NOTICE ''leave fr_d ....''; RETURN OLD; END; 'LANGUAGE plpgsql; GRANT ALL ON FUNCTION feature_relationship_fn_d() TO PUBLIC; CREATE TRIGGER feature_relationship_tr_d BEFORE DELETE ON feature_relationship for EACH ROW EXECUTE PROCEDURE feature_relationship_fn_d(); DROP TABLE trigger_log; CREATE TABLE trigger_log( value varchar(255) not null, timeaccessioned timestamp not null default current_timestamp, table_name varchar(50), id bigint ); GRANT ALL ON TABLE trigger_log TO PUBLIC; DROP TRIGGER feature_relationship_propagatename_tr_i ON feature_relationship; CREATE OR REPLACE FUNCTION feature_relationship_propagatename_fn_i() RETURNS TRIGGER AS ' DECLARE maxid bigint; exon_id bigint; id varchar(255); maxid_fb bigint; id_fb varchar(255); loginfo varchar(255); len bigint; prefix varchar; suffix varchar; f_row_g feature%ROWTYPE; f_row_e feature%ROWTYPE; f_row_t feature%ROWTYPE; f_row_p feature%ROWTYPE; fl_row_e featureloc%ROWTYPE; f_type cvterm.name%TYPE; f_type_temp cvterm.name%TYPE; letter_t varchar(100); letter_p varchar(100); f_dbxref_id feature.dbxref_id%TYPE; fb_accession dbxref.accession%TYPE; d_accession dbxref.accession%TYPE; f_name_gene feature.name%TYPE; f_name feature.name%TYPE; f_d_id feature_dbxref.feature_dbxref_id%TYPE; dx_id dbxref.dbxref_id%TYPE; d_id db.db_id%TYPE; s_type_id synonym.type_id%TYPE; s_id synonym.synonym_id%TYPE; p_id pub.pub_id%TYPE; p_type_id cvterm.cvterm_id%TYPE; c_cv_id cv.cv_id%TYPE; f_s_id feature_synonym.feature_synonym_id%TYPE; fr_row feature_relationship%ROWTYPE; f_accession_temp varchar(255); f_accession varchar(255); f_type_gene CONSTANT varchar :=''gene''; f_type_exon CONSTANT varchar :=''exon''; f_type_transcript CONSTANT varchar :=''mRNA''; f_type_snoRNA CONSTANT varchar :=''snoRNA''; f_type_ncRNA CONSTANT varchar :=''ncRNA''; f_type_snRNA CONSTANT varchar :=''snRNA''; f_type_tRNA CONSTANT varchar :=''tRNA''; f_type_promoter CONSTANT varchar :=''promoter''; f_type_repeat_region CONSTANT varchar :=''repeat_region''; f_type_miRNA CONSTANT varchar :=''miRNA''; f_type_transposable_element CONSTANT varchar :=''transposable_element''; f_type_rRNA CONSTANT varchar :=''rRNA''; f_type_pseudo CONSTANT varchar :=''pseudogene''; f_type_protein CONSTANT varchar :=''polypeptide''; f_type_allele CONSTANT varchar :=''alleleof''; f_dbname_gadfly CONSTANT varchar :=''DB:GR''; f_dbname_FB CONSTANT varchar :=''null''; c_name_synonym CONSTANT varchar:=''synonym''; cv_cvname_synonym CONSTANT varchar:=''null''; p_miniref CONSTANT varchar:=''none''; p_cvterm_name CONSTANT varchar:=''computer file''; p_cv_name CONSTANT varchar:=''pub type''; fng_type_id cvterm.cvterm_id%TYPE; BEGIN SELECT INTO prefix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''prefix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO suffix cp.value FROM cvtermprop cp, cvterm, cv WHERE cvterm.name = ''suffix'' and cp.cvterm_id = cvterm.cvterm_id and cvterm.cv_id = cv.cv_id and cv.name = ''apollo''; SELECT INTO p_id pub_id from pub where uniquename = p_miniref; SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id; RAISE NOTICE ''propagating names, prefix:%, suffix:%'',prefix,suffix; RAISE NOTICE ''enter fr_i, fr.object_id:%, fr.subject_id:%'', NEW.object_id, NEW.subject_id; SELECT INTO f_type c.name from feature f, cvterm c where f.type_id=c.cvterm_id and f.feature_id=NEW.object_id; SELECT INTO f_name name from feature where feature_id = NEW.subject_id; -- OK, the thing having a child added is a gene IF ((f_name IS NULL OR f_name like prefix||''%temp%'') and f_type=f_type_gene) THEN SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=NEW.subject_id and f.type_id=c.cvterm_id; IF (f_type_temp=f_type_transcript or f_type_temp=f_type_snoRNA or f_type_temp=f_type_ncRNA or f_type_temp=f_type_snRNA or f_type_temp=f_type_tRNA or f_type_temp=f_type_rRNA or f_type_temp=f_type_miRNA or f_type_temp=f_type_pseudo or f_type_temp=f_type_transposable_element or f_type_temp=f_type_promoter or f_type_temp=f_type_repeat_region) THEN --generate a new name based on the gene name --the name is like: genename-transcript# SELECT INTO f_name_gene name from feature where feature_id=NEW.object_id; -- SELECT INTO maxid to_number(max(substring(name from (length(f_name_gene)+1+10))), ''99999'') FROM feature where name like f_name_gene||''-transcript%''; SELECT INTO c_cv_id cv_id FROM cv WHERE name = ''Sequence Ontology Feature Annotation''; SELECT INTO fng_type_id cvterm_id FROM cvterm WHERE name = ''transcript'' AND cv_id = c_cv_id; SELECT INTO maxid count FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id; IF maxid IS NULL THEN maxid = 1; ELSE maxid = maxid + 1; END IF; f_name:=CAST(f_name_gene||''-transcript''||maxid AS TEXT); RAISE NOTICE ''start to update feature, gene name:%, new feature name:%'', f_name_gene, f_name; UPDATE feature set name=f_name where feature_id=NEW.subject_id; DELETE FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id; INSERT INTO feature_namegenerator (name,type_id,count) VALUES (f_name_gene, fng_type_id, maxid); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; END IF; RAISE NOTICE ''start to insert feature_synonym:synonym_id:%,feature_id:%, pub_id:%'', s_id, NEW.subject_id, p_id; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=NEW.subject_id and synonym_id=s_id and pub_id=p_id; IF f_s_id IS NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (NEW.subject_id, s_id, p_id, ''true''); END IF; END IF; -- here the thing having a child added is a second level thing (eg, a transcript is getting an exon or protein) ELSIF ((f_name IS NULL OR f_name like prefix||''%temp%'') and (f_type=f_type_transcript or f_type=f_type_ncRNA or f_type=f_type_snoRNA or f_type=f_type_snRNA or f_type=f_type_tRNA or f_type=f_type_rRNA or f_type=f_type_miRNA or f_type=f_type_pseudo or f_type=f_type_transposable_element or f_type=f_type_promoter or f_type=f_type_repeat_region) ) THEN SELECT INTO f_name_gene f.name from feature f, feature_relationship fr, cvterm c where f.feature_id=fr.object_id and fr.subject_id=NEW.object_id and f.type_id=c.cvterm_id and c.name=f_type_gene; SELECT INTO f_type_temp c.name from feature f, cvterm c where f.feature_id=NEW.subject_id and f.type_id=c.cvterm_id; --adding a protein to a transcript IF f_type_temp=f_type_protein THEN IF f_name_gene IS NOT NULL THEN SELECT INTO f_row_p * from feature where feature_id=NEW.subject_id; --create a new name for this protein (again repeating code in assign_names) --SELECT INTO maxid to_number(max(substring(name from (length(f_name_gene)+1+12))), ''99999'') FROM feature where name like f_name_gene||''-polypeptide%''; SELECT INTO c_cv_id cv_id FROM cv WHERE name = ''Sequence Ontology''; SELECT INTO fng_type_id cvterm_id FROM cvterm WHERE name = f_type_protein AND cv_id = c_cv_id; SELECT INTO maxid count FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id; IF maxid IS NULL THEN maxid = 1; ELSE maxid = maxid + 1; END IF; f_name:=CAST(f_name_gene||''-''||f_type_protein||maxid AS TEXT); RAISE NOTICE ''update name of polypeptide:% to new name:%'',f_row_p.name, f_name; UPDATE feature set name=f_name where feature_id=NEW.subject_id; DELETE FROM feature_namegenerator WHERE name = f_name_gene AND type_id = fng_type_id; INSERT INTO feature_namegenerator (name,type_id,count) VALUES (f_name_gene, fng_type_id, maxid); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_p.feature_id and synonym_id=s_id; IF f_s_id is NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_p.feature_id, s_id, p_id, ''true''); END IF; ELSE RAISE NOTICE ''Couldnt find a gene to add this polypeptide to (feature_id:%)'', NEW.subject_id ; END IF; --adding an exon to a transcript ELSIF f_type_temp=f_type_exon THEN IF f_name_gene IS NOT NULL THEN SELECT INTO f_row_e * from feature where feature_id=NEW.subject_id; SELECT INTO fl_row_e * from featureloc where feature_id = NEW.subject_id and rank=0; IF fl_row_e.fmin IS NULL OR fl_row_e.fmax IS NULL THEN RAISE NOTICE ''cant create exon name for feature_id % since there is no featureloc entry'', NEW.subject_id; RETURN NEW; ELSE f_name:=CAST(f_name_gene||'':''||fl_row_e.fmin||''-''||fl_row_e.fmax AS TEXT); END IF; RAISE NOTICE ''exon new name:%'', f_name; UPDATE feature set name=f_name where feature_id=NEW.subject_id; SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(f_name, f_name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=f_name and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row_e.feature_id and synonym_id=s_id; IF f_s_id is NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row_e.feature_id, s_id, p_id, ''true''); END IF; ELSE RAISE NOTICE ''Couldnt find a gene to add this exon to (feature_id:%)'',NEW.subject_id; END IF; END IF; ELSE --nothing to do, the name is not null or containing temp -- RAISE NOTICE ''no link to gene for this transcript or wrong feature_relationship: transcript->polypeptide/exon:object_id:%, subject_id:%'', NEW.object_id, NEW.subject_id; END IF; RAISE NOTICE ''leave fr_i ....''; RETURN NEW; END; 'LANGUAGE plpgsql; GRANT ALL ON FUNCTION feature_relationship_propagatename_fn_i() TO PUBLIC; CREATE TRIGGER feature_relationship_propagatename_tr_i AFTER INSERT ON feature_relationship FOR EACH ROW EXECUTE PROCEDURE feature_relationship_propagatename_fn_i(); DROP TRIGGER feature_update_name_tr_u ON feature; CREATE OR REPLACE FUNCTION feature_fn_u() RETURNS TRIGGER AS ' DECLARE f_type cvterm.name%TYPE; f_type_gene CONSTANT varchar :=''gene''; f_row feature%ROWTYPE; s_type_id synonym.type_id%TYPE; s_id synonym.synonym_id%TYPE; f_s_id feature_synonym.feature_synonym_id%TYPE; p_id pub.pub_id%TYPE; p_miniref CONSTANT varchar:=''none''; c_name_synonym CONSTANT varchar:=''synonym''; cv_cvname_synonym CONSTANT varchar:=''null''; name_suffix varchar; child_name varchar; BEGIN IF OLD.uniquename <> NEW.uniquename AND OLD.uniquename NOT LIKE ''%temp%'' THEN RAISE NOTICE ''You may not change the uniquename of a feature''; RAISE NOTICE ''if you feel you must, contact your database admin''; RETURN OLD; END IF; IF OLD.name = NEW.name THEN --not updating name, so go ahead RETURN NEW; END IF; --fetch a few useful things SELECT INTO s_type_id cvterm_id from cvterm c1, cv c2 where c1.name=c_name_synonym and c2.name=cv_cvname_synonym and c1.cv_id=c2.cv_id; SELECT INTO f_type cv.name FROM feature f, cvterm cv WHERE f.feature_id = OLD.feature_id and f.type_id = cv.cvterm_id; SELECT INTO p_id pub_id from pub where uniquename = p_miniref; -- IF f_type <> f_type_gene THEN -- --its not a gene, so go ahead -- --but insert the new name into the synonym table --On gene name update, we also update the gene inner synonym (ie himself) --so for all features, the inner synonym gets updated IF NEW.name IS NOT NULL THEN SELECT INTO s_id synonym_id from synonym where name=NEW.name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(NEW.name, NEW.name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=NEW.name and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=NEW.feature_id and synonym_id=s_id and pub_id=p_id; IF f_s_id IS NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (NEW.feature_id, s_id, p_id, ''true''); END IF; END IF; IF f_type <> f_type_gene THEN --its not a gene, so go ahead RETURN NEW; END IF; --OK, so its a gene, and were changing the name... --For corectly handling alternate transcript of a gene, we must add a distinct in order not to process the same exon multiple times (exons are shared in chado) FOR f_row IN SELECT DISTINCT f.* FROM feature f, get_sub_feature_ids(OLD.feature_id) ch WHERE f.feature_id = ch.feature_id LOOP --This one process the transcripts and the proteins -- IF f_row.name LIKE OLD.name||''-%'' THEN -- SELECT INTO name_suffix substring(name from OLD.name||''(-.+)'') FROM feature where feature_id = f_row.feature_id; -- child_name = NEW.name||name_suffix; -- UPDATE feature SET name = child_name WHERE feature_id = f_row.feature_id; -- SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id; -- IF s_id IS NULL THEN -- INSERT INTO synonym(name, synonym_sgml, type_id) values(child_name, child_name, s_type_id); -- SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id; -- END IF; -- SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row.feature_id and synonym_id=s_id and pub_id=p_id; -- IF f_s_id IS NULL THEN -- INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row.feature_id, s_id, p_id, ''true''); -- END IF; -- END IF; --And this one process the exons (of form gene_name:something) --Be careful, this could lead to problems with some autogenerated split names which for flybase are also of the form gene_name:#) TODO : move this regexp to something like /gene_name:\d-\d/ IF f_row.name LIKE OLD.name||'':%'' THEN SELECT INTO name_suffix substring(name from OLD.name||''(:.+)'') FROM feature where feature_id = f_row.feature_id; child_name = NEW.name||name_suffix; RAISE NOTICE ''feature_id:%, name:% , new name : %, namesuffix:%, geneName:%'', f_row.feature_id, f_row.name, child_name,name_suffix, NEW.name ; UPDATE feature SET name = child_name WHERE feature_id = f_row.feature_id; SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id; IF s_id IS NULL THEN INSERT INTO synonym(name, synonym_sgml, type_id) values(child_name, child_name, s_type_id); SELECT INTO s_id synonym_id from synonym where name=child_name and type_id=s_type_id; END IF; SELECT INTO f_s_id feature_synonym_id from feature_synonym where feature_id=f_row.feature_id and synonym_id=s_id and pub_id=p_id; IF f_s_id IS NULL THEN INSERT INTO feature_synonym(feature_id, synonym_id, pub_id, is_current) values (f_row.feature_id, s_id, p_id, ''true''); END IF; END IF; END LOOP; RETURN NEW; END; 'LANGUAGE plpgsql; CREATE TRIGGER feature_update_name_tr_u BEFORE UPDATE ON feature FOR EACH ROW EXECUTE PROCEDURE feature_fn_u(); -- function to fascilitate gene merges -- returns the uniquename of the merged gene --WARNING: COMPLETELY UNTESTED CREATE OR REPLACE FUNCTION apollo_merge(varchar, varchar) RETURNS varchar AS ' DECLARE f1_uniquename ALIAS FOR $1; f2_uniquename ALIAS FOR $2; f_uniquename feature.uniquename%TYPE; f1_fid feature.feature_id%TYPE; f2_fid feature.feature_id%TYPE; f_id feature.feature_id%TYPE; f1_type cvterm.name%TYPE; f2_type cvterm.name%TYPE; f1_organism_id feature.organism_id%TYPE; f2_organism_id feature.organism_id%TYPE; f_type_gene CONSTANT varchar :=''gene''; c_cvterm_id cvterm.cvterm_id%TYPE; s_id synonym.synonym_id%TYPE; dbxref_id dbxref.dbxref_id%TYPE; fd_row feature_dbxref%ROWTYPE; p_id pub.pub_id%TYPE; fpub_row feature_pub%ROWTYPE; fp_row featureprop%ROWTYPE; fc_row feature_cvterm%ROWTYPE; fs_row feature_synonym%ROWTYPE; BEGIN --check that they are both genes SELECT INTO f1_type c.name FROM feature f, cvterm c WHERE f.uniquename = f1_uniquename AND f.type_id = c.cvterm_id; SELECT INTO f2_type c.name FROM feature f, cvterm c WHERE f.uniquename = f1_uniquename AND f.type_id = c.cvterm_id; IF f1_type <> f_type_gene OR f2_type <> f_type_gene THEN RAISE NOTICE ''The uniquenames for the features provided must be those of genes''; RETURN OLD; END IF; --check that they come from the same organism SELECT INTO f1_organism_id FROM feature WHERE uniquename=f1_uniquename; SELECT INTO f2_organism_id FROM feature WHERE uniquename=f2_uniquename; IF f1_organism_id <> f2_organism_id THEN RAISE NOTICE ''The merged genes must come from the same organism''; RETURN OLD; END IF; SELECT INTO c_cvterm_id type_id FROM feature WHERE uniquename = f1_uniquename; SELECT INTO f_uniquename next_uniquename(); --create the new gene INSERT INTO feature (organism_id, uniquename, type_id) VALUES (f1_organism_id, f_uniquename, c_cvterm_id); SELECT INTO f_id feature_id FROM feature WHERE uniquename = f_uniquename AND organism_id = f_organism_id AND type_id = c_cvterm_id; SELECT INTO f1_id feature_id FROM feature WHERE uniquename = f1_uniquename AND organism_id = f1_organism_id AND type_id = c_cvterm_id; SELECT INTO f2_id feature_id FROM feature WHERE uniquename = f2_uniquename AND organism_id = f2_organism_id AND type_id = c_cvterm_id; --add synonyms to new feature for all of the synonyms of the old feature FOR fs_row IN SELECT * FROM feature_synonym WHERE feature_id = f1_id OR feature_id = f2_id LOOP INSERT INTO feature_synonym (feature_id, synonym_id) VALUES (f_id, fs_row.synonym_id); END LOOP; --copy dbxref, featureprop, cvterms, pubs to the new gene FOR fd_row IN SELECT * FROM feature_dbxref WHERE feature_id = f1_id OR feature_id = f2_id LOOP INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current) VALUES (f_id, fd_row.dbxref_id, 0); END LOOP; FOR fp_row IN SELECT * FROM featureprop WHERE feature_id = f1_id OR feature_id = f2_id LOOP INSERT INTO featureprop (feature_id,type_id,value,rank) VALUES (f_id,fp_row.type_id,fp_row.value,fp_row.rank); END LOOP; FOR fc_row IN SELECT * FROM feature_cvterm WHERE feature_id = f1_id OR feature_id = f2_id LOOP INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not) VALUES (f_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not); END LOOP; FOR fpub_row IN SELECT * FROM feature_pub WHERE feature_id = f1_id OR feature_id = f2_id LOOP INSERT INTO feature_pub (feature_id,pub_id) VALUES (f_id,fpub_row.pub_id); END LOOP; --delete featureloc entries for old genes DELETE FROM featureloc WHERE feature_id=f1_id; DELETE FROM featureloc WHERE feature_id=f2_id; --mark old gene features as obsolete UPDATE feature SET is_obsolete=true WHERE feature_id=f1_id; UPDATE feature SET is_obsolete=true WHERE feature_id=f2_id; --note in either feature_relationship or featureprop the origin of this gene RETURN f_uniquename; END; 'LANGUAGE plpgsql; -- function to fascilitate gene splits -- Takes three arguments: -- -the feature_id of the old gene that was split -- -the feature_id of the new genes --WARNING: COMPLETELY UNTESTED CREATE OR REPLACE FUNCTION apollo_split(bigint,bigint,bigint) RETURNS bigint AS ' DECLARE f_id ALIAS FOR $1; f1_id ALIAS FOR $2; f2_id ALIAS FOR $3; f_type cvterm.name%TYPE; f_organism_id feature.organism_id%TYPE; f1_organism_id feature.organism_id%TYPE; f2_organism_id feature.organism_id%TYPE; f_type_gene CONSTANT varchar :=''gene''; c_cvterm_id cvterm.cvterm_id%TYPE; fs_row feature_synonym%ROWTYPE; fd_row feature_dbxref%ROWTYPE; fpub_row pub%ROWTYPE; fp_row featureprop%ROWTYPE; fc_row feature_cvterm%ROWTYPE; BEGIN --do some error checking; first, make sure they are all genes SELECT INTO f_type c.name FROM feature f, cvterm c WHERE f.feature_id = f_id AND f.type_id = c.cvterm_id; IF f_type <> f_type_gene THEN RAISE NOTICE ''The uniquename for the feature provided must be that of a gene''; RETURN OLD; END IF; SELECT INTO f_type c.name FROM feature f, cvterm c WHERE f.feature_id = f1_id AND f.type_id = c.cvterm_id; IF f_type <> f_type_gene THEN RAISE NOTICE ''The uniquename for the feature provided must be that of a gene''; RETURN OLD; END IF; SELECT INTO f_type c.name FROM feature f, cvterm c WHERE f.feature_id = f2_id AND f.type_id = c.cvterm_id; IF f_type <> f_type_gene THEN RAISE NOTICE ''The uniquename for the feature provided must be that of a gene''; RETURN OLD; END IF; --more error checking: are they all from the same organism? SELECT INTO f_organism_id organism_id FROM feature WHERE feature_id = f_id; SELECT INTO f1_organism_id organism_id FROM feature WHERE feature_id = f1_id; SELECT INTO f2_organism_id organism_id FROM feature WHERE feature_id = f2_id; IF f_organism_id <> f1_organism_id OR f_organism_id <> f2_organism_id THEN RAISE NOTICE ''The new gene and the old genes need to come from the same organism''; RETURN OLD; END IF; SELECT INTO c_cvterm_id type_id FROM feature WHERE feature_id = f_id; --add synonyms to new feature for all of the synonyms of the old feature FOR fs_row IN SELECT * FROM feature_synonym WHERE feature_id = f_id LOOP INSERT INTO feature_synonym (feature_id, synonym_id, pub_id) VALUES (f1_id, fs_row.synonym_id, fs_row.pub_id); INSERT INTO feature_synonym (feature_id, synonym_id, pub_id) VALUES (f2_id, fs_row.synonym_id, fs_row.pub_id); END LOOP; --copy dbxref, featureprop, cvterms, pubs to the new gene FOR fd_row IN SELECT * FROM feature_dbxref WHERE feature_id = f_id LOOP INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current) VALUES (f1_id, fd_row.dbxref_id, 0); INSERT INTO feature_dbxref (feature_id,dbxref_id,is_current) VALUES (f2_id, fd_row.dbxref_id, 0); END LOOP; FOR fp_row IN SELECT * FROM featureprop WHERE feature_id = f_id LOOP INSERT INTO featureprop (feature_id,type_id,value,rank) VALUES (f1_id,fp_row.type_id,fp_row.value,fp_row.rank); INSERT INTO featureprop (feature_id,type_id,value,rank) VALUES (f2_id,fp_row.type_id,fp_row.value,fp_row.rank); END LOOP; FOR fc_row IN SELECT * FROM feature_cvterm WHERE feature_id = f_id LOOP INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not) VALUES (f1_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not); INSERT INTO feature_cvterm (feature_id,cvterm_id,pub_id,is_not) VALUES (f2_id,fc_row.cvterm_id,fc_row.pub_id,fc_row.is_not); END LOOP; FOR fpub_row IN SELECT * FROM feature_pub WHERE feature_id = f_id LOOP INSERT INTO feature_pub (feature_id,pub_id) VALUES (f1_id,p_row.pub_id); INSERT INTO feature_pub (feature_id,pub_id) VALUES (f2_id,p_row.pub_id); END LOOP; --delete featureloc entries for old genes DELETE FROM featureloc WHERE feature_id=f_id; --mark old gene features as obsolete UPDATE feature SET is_obsolete=true WHERE feature_id=f_id; --note in either feature_relationship or featureprop the origin of this gene RETURN 1; END; 'LANGUAGE plpgsql;