Project

General

Profile

« Previous | Next » 

Revision 14304

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: taxon_match: added derived column "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"

View differences:

schema.sql
313 313 
		"*Unmatched_terms"
314 314 
		FROM (SELECT new.*) new);
315 315
316 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT
317 
		CASE
318 
        WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
319 
        WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
320 
        ELSE "*Accepted_name_species"
321 
    END
322 
		FROM (SELECT new.*) new);
323
316 324 
	RETURN new;
317 325 
END;
318 326 
$$;
......
465 473 
    "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text,
466 474 
    "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text,
467 475 
    "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text,
476 
    "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text,
468 477 
    CONSTRAINT "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[1]))),
469 478 
    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[1]))),
470 479 
    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
480 
    CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
481 
CASE
482 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
483 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
484 
    ELSE "*Accepted_name_species"
485 
END))),
471 486 
    CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),
472 487 
    CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
473 488 
    CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))),
......
1060 1075 

  		




  1061
  1076
  
    

  		




  1062
  1077
  
    
--


  		




  
  1078
  
    
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  1079
  
    
--


  		




  
  1080
  
    

  		




  
  1081
  
    
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '


  		




  
  1082
  
    
= 


  		




  
  1083
  
    
        CASE


  		




  
  1084
  
    
            WHEN "*Accepted_name_rank" = ''family''::text THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1085
  
    
            WHEN "*Accepted_name_rank" = ''genus''::text THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1086
  
    
            ELSE "*Accepted_name_species"


  		




  
  1087
  
    
        END


  		




  
  1088
  
    

  		




  
  1089
  
    
derived column


  		




  
  1090
  
    

  		




  
  1091
  
    
to modify expr:


  		




  
  1092
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE


  		




  
  1093
  
    
        WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1094
  
    
        WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1095
  
    
        ELSE "*Accepted_name_species"


  		




  
  1096
  
    
    END$$)::util.derived_col_def);


  		




  
  1097
  
    
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);


  		




  
  1098
  
    

  		




  
  1099
  
    
to rename:


  		




  
  1100
  
    
# rename column


  		




  
  1101
  
    
# rename CHECK constraint


  		




  
  1102
  
    
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);


  		




  
  1103
  
    
';


  		




  
  1104
  
    

  		




  
  1105
  
    

  		




  
  1106
  
    
--


  		




  1063
  1107
  
    
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: -


  		




  1064
  1108
  
    
--


  		




  1065
  1109
  
    

  		












Also available in:  Unified diff