SQL to get data in GO Annotation File Format (GAF)

go_annotations.sql

SELECT 'dictyBase' db, /* Column 1 */
  GENE_ID.accession db_id, /* Column 2 */
  GENE.uniquename gene_symbol, /* Column 3 */
  TO_CHAR(q_prop.value) qualifier, /* Column 4 */
  'GO' || ':' || GO_ID.accession go_id, /* Column 5 */
  'PMID' || ':' || pub.uniquename db_reference, /* Column 6 */
  evsyn.synonym_ evidence_code, /* Column 7 */
  TO_CHAR(with_prop.value) with_from, /* Column 8 */
  cv.name aspect, /* Column 9 */
  GENE.name object_name, /* Column 10 */
  '' object_synonym, /* Column 11 */
  type.name object_type, /* Column 12 */
  'taxon' || ':' || '44689' taxon, /* Column 13 */
  TO_CHAR(date_prop.value) date_created, /* Column 14 */
  TO_CHAR(source_prop.value) assigned_by, /* Column 15 */
  '' annotation_extension, /* Column 16 */
  '' gp_id /* Column 17 */

FROM feature_cvterm fcvt

/* Gene */
JOIN feature GENE ON GENE.feature_id = fcvt.feature_id
JOIN dbxref GENE_ID ON GENE_ID.dbxref_id = GENE.dbxref_id
JOIN cvterm type ON type.cvterm_id = GENE.type_id

/* Qualifier */
JOIN feature_cvtermprop q_prop ON q_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm qterm ON qterm.cvterm_id = q_prop.type_id

/* Date created */
JOIN feature_cvtermprop date_prop ON date_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm date_term ON date_term.cvterm_id = date_prop.type_id

/* Assigned by */
JOIN feature_cvtermprop source_prop ON source_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm source_term ON source_term.cvterm_id = source_prop.type_id

/* GO */
JOIN cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cv ON cv.cv_id = GO.cv_id
JOIN dbxref GO_ID ON GO_ID.dbxref_id = GO.dbxref_id
JOIN db ON db.db_id = GO_ID.db_id

/* Publication */
JOIN pub ON pub.pub_id = fcvt.pub_id

/* Evidence code */
JOIN feature_cvtermprop ev_prop ON ev_prop.feature_cvterm_id=fcvt.feature_cvterm_id
JOIN cvterm evterm ON evterm.cvterm_id=ev_prop.type_id
JOIN cv ev ON ev.cv_id=evterm.cv_id
JOIN cvtermsynonym evsyn on evterm.cvterm_id=evsyn.cvterm_id

/* Organism */
JOIN organism ON organism.organism_id = gene.organism_id

/* With-From */
JOIN feature_cvtermprop with_prop ON with_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm with_term ON with_term.cvterm_id = with_prop.type_id

WHERE cv.name IN('molecular_function',   'biological_process',   'cellular_component')
AND db.name = 'GO'
AND GO.is_obsolete = 0

AND ev.name like 'evidence_code%'

AND qterm.name = 'qualifier'
AND date_term.name = 'date'
AND with_term.name = 'with'
AND source_term.name = 'source'

AND pub.pubplace = 'PUBMED'
AND organism.common_name = 'dicty'
;