Project

General

Profile

« Previous | Next » 

Revision 14318

Added by Aaron Marcuse-Kubitza over 10 years ago

fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_family__@TNRS__@vegpath.org", which is needed because "*Accepted_name_family" isn't always populated

View differences:

vegbien.sql
19262 19262 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19263 19263 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
19264 19264 
	new.matched_has_accepted = NULL;
19265 
	new."Accepted_family__@TNRS__@vegpath.org" = NULL;
19265 19266 
	new."__accepted_{genus,specific_epithet}" = NULL;
19266 19267 
	new."[accepted_]genus__@DwC__@vegpath.org" = NULL;
19267 19268 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;
......
19287 19288 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
19288 19289 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
19289 19290 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
19291 
	new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family",
19292 
CASE
19293 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
19294 
    ELSE NULL::text
19295 
END) FROM (SELECT new.*) new);
19290 19296 
	new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
19291 19297 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[1] FROM (SELECT new.*) new);
19292 19298 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
......
19517 19523 
    "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text,
19518 19524 
    "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text,
19519 19525 
    matched_has_accepted boolean,
19526 
    "Accepted_family__@TNRS__@vegpath.org" text,
19520 19527 
    "__accepted_{genus,specific_epithet}" text[],
19521 19528 
    "[accepted_]genus__@DwC__@vegpath.org" text,
19522 19529 
    "[accepted_]specificEpithet__@DwC__@vegpath.org" text,
......
19536 19543 
    "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text,
19537 19544 
    "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text,
19538 19545 
    "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text,
19546 
    CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family",
19547 
CASE
19548 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
19549 
    ELSE NULL::text
19550 
END)))),
19539 19551 
    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]))),
19540 19552 
    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[1]))),
19541 19553 
    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
......
19719 19731 

  		




  19720
  19732
  
    

  		




  19721
  19733
  
    
--


  		




  
  19734
  
    
-- Name: COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  19735
  
    
--


  		




  
  19736
  
    

  		




  
  19737
  
    
COMMENT ON COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org" IS '


  		




  
  19738
  
    
= COALESCE("*Accepted_name_family",


  		




  
  19739
  
    
CASE


  		




  
  19740
  
    
    WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"


  		




  
  19741
  
    
    ELSE NULL::text


  		




  
  19742
  
    
END)


  		




  
  19743
  
    

  		




  
  19744
  
    
derived column


  		




  
  19745
  
    

  		




  
  19746
  
    
to modify expr:


  		




  
  19747
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col, $$COALESCE("*Accepted_name_family",


  		




  
  19748
  
    
CASE


  		




  
  19749
  
    
    WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"


  		




  
  19750
  
    
    ELSE NULL::text


  		




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


  		




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


  		




  
  19753
  
    

  		




  
  19754
  
    
to rename:


  		




  
  19755
  
    
# rename column


  		




  
  19756
  
    
# rename CHECK constraint


  		




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


  		




  
  19758
  
    
';


  		




  
  19759
  
    

  		




  
  19760
  
    

  		




  
  19761
  
    
--


  		




  19722
  19762
  
    
-- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet}"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  19723
  19763
  
    
--


  		




  19724
  19764
  
    

  		












Also available in:  Unified diff