Project

General

Profile

« Previous | Next » 

Revision 9985

Added by Aaron Marcuse-Kubitza over 11 years ago

inputs/.TNRS/schema.sql: added family_is_homonym(), genus_is_homonym() and use them in tnrs_populate_fields()

View differences:

inputs/.TNRS/schema.sql
6 6 
  LANGUAGE sql IMMUTABLE STRICT
7 7 
  COST 100;
8 8 

  		




  
  9
  
    
CREATE OR REPLACE FUNCTION family_is_homonym(family text)


  		




  
  10
  
    
  RETURNS boolean AS


  		




  
  11
  
    
$BODY$


  		




  
  12
  
    
SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)


  		




  
  13
  
    
$BODY$


  		




  
  14
  
    
  LANGUAGE sql STABLE


  		




  
  15
  
    
  COST 100;


  		




  
  16
  
    

  		




  
  17
  
    
CREATE OR REPLACE FUNCTION genus_is_homonym(genus text)


  		




  
  18
  
    
  RETURNS boolean AS


  		




  
  19
  
    
$BODY$


  		




  
  20
  
    
SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)


  		




  
  21
  
    
$BODY$


  		




  
  22
  
    
  LANGUAGE sql STABLE


  		




  
  23
  
    
  COST 100;


  		




  
  24
  
    

  		




  9
  25
  
    
/* IMPORTANT: when changing this table's schema, you must regenerate data.sql:


  		




  10
  26
  
    
$ <this_file>/../test_taxonomic_names/test_scrub


  		




  11
  27
  
    
*/


  		




  ......




  82
  98
  
    
        ELSE NULL -- ambiguous


  		




  83
  99
  
    
        END);


  		




  84
  100
  
    
    never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); -- author disambiguates


  		




  85
  
  
    
    family_is_homonym boolean = NOT never_homonym AND EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = new."Family_matched");


  		




  86
  
  
    
    genus_is_homonym  boolean = NOT never_homonym AND EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet  WHERE "taxonNameOrEpithet" = new."Genus_matched");


  		




  
  101
  
    
    family_is_homonym boolean = NOT never_homonym AND family_is_homonym(new."Family_matched");


  		




  
  102
  
    
    genus_is_homonym  boolean = NOT never_homonym AND genus_is_homonym( new."Genus_matched" );


  		




  87
  103
  
    
BEGIN


  		




  88
  104
  
    
    new."Accepted_scientific_name" = NULLIF(concat_ws(' '


  		




  89
  105
  
    
        , NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")


  		












Also available in:  Unified diff