Project

General

Profile

« Previous | Next » 

Revision 14377

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": don't need to use *Accepted_name anymore because _accepted{genus,specific_epithet,infra_{rank,epithet}} is now generated from *Accepted_name

View differences:

vegbien.sql
19877 19877 
END) FROM (SELECT new.*) new);
19878 19878 
	new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
19879 19879 
	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = (SELECT regexp_split_to_array("*Accepted_name", ' '::text) FROM (SELECT new.*) new);
19880 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1],
19881 
CASE
19882 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
19883 
    ELSE NULL::text
19884 
END) FROM (SELECT new.*) new);
19880 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1] FROM (SELECT new.*) new);
19885 19881 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2] FROM (SELECT new.*) new);
19886 19882 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
19887 19883 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
......
20147 20143 
END)))),
20148 20144 
    CONSTRAINT "Accepted_species[_binomial]__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_species[_binomial]__@TNRS__@vegpath.org" IS DISTINCT FROM rtrim("*Accepted_name_species", ' '::text)))),
20149 20145 
    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]))),
20150 
    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM COALESCE("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1],
20151 
CASE
20152 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
20153 
    ELSE NULL::text
20154 
END)))),
20146 
    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]))),
20155 20147 
    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
20156 20148 
    CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
20157 20149 
CASE
......
20440 20432 
--
20441 20433 

  		




  20442
  20434
  
    
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS '


  		




  20443
  
  
    
= COALESCE("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1],


  		




  20444
  
  
    
CASE


  		




  20445
  
  
    
    WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name"


  		




  20446
  
  
    
    ELSE NULL::text


  		




  20447
  
  
    
END)


  		




  
  20435
  
    
= "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]


  		




  20448
  20436
  
    

  		




  20449
  20437
  
    
derived column


  		




  20450
  20438
  
    

  		




  20451
  20439
  
    
to modify expr:


  		




  20452
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$COALESCE("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1],


  		




  20453
  
  
    
CASE


  		




  20454
  
  
    
    WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name"


  		




  20455
  
  
    
    ELSE NULL::text


  		




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


  		




  
  20440
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]$$)::util.derived_col_def);


  		




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


  		




  20458
  20442
  
    

  		




  20459
  20443
  
    
to rename:


  		












Also available in:  Unified diff