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:

trunk/inputs/.TNRS/schema.sql
258 258 
END) FROM (SELECT new.*) new);
259 259 
	new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
260 260 
	new."__accepted_{genus,specific_epithet,infra_{rank,epithet}}" = (SELECT regexp_split_to_array("*Accepted_name", ' '::text) FROM (SELECT new.*) new);
261 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1],
262 
CASE
263 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
264 
    ELSE NULL::text
265 
END) FROM (SELECT new.*) new);
261 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1] FROM (SELECT new.*) new);
266 262 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[2] FROM (SELECT new.*) new);
267 263 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
268 264 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
......
486 482 
END)))),
487 483 
    CONSTRAINT "Accepted_species[_binomial]__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_species[_binomial]__@TNRS__@vegpath.org" IS DISTINCT FROM rtrim("*Accepted_name_species", ' '::text)))),
488 484 
    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]))),
489 
    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],
490 
CASE
491 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
492 
    ELSE NULL::text
493 
END)))),
485 
    CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet,infra_{rank,epithet}}"[1]))),
494 486 
    CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
495 487 
    CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
496 488 
CASE
......
779 771 
--
780 772 

  		




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


  		




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


  		




  783
  
  
    
CASE


  		




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


  		




  785
  
  
    
    ELSE NULL::text


  		




  786
  
  
    
END)


  		




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


  		




  787
  775
  
    

  		




  788
  776
  
    
derived column


  		




  789
  777
  
    

  		




  790
  778
  
    
to modify expr:


  		




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


  		




  792
  
  
    
CASE


  		




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


  		




  794
  
  
    
    ELSE NULL::text


  		




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


  		




  
  779
  
    
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);


  		




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


  		




  797
  781
  
    

  		




  798
  782
  
    
to rename:


  		












  

    
      trunk/schemas/vegbien.my.sql
    
  





  15987
  15987
  
    
CASE


  		




  15988
  15988
  
    
    WHEN (`*Accepted_name_rank` = CAST('family' AS varchar(255))) THEN `*Accepted_name`


  		




  15989
  15989
  
    
    ELSE NULL::varchar(255)


  		




  15990
  
  
    
END)))),


  		




  15991
  
  
    
CASE


  		




  15992
  
  
    
    WHEN (`*Accepted_name_rank` = CAST('genus' AS varchar(255))) THEN `*Accepted_name`


  		




  15993
  
  
    
    ELSE NULL::varchar(255)


  		




  15994
  15990
  
    
END))))


  		




  15995
  15991
  
    
CASE


  		




  15996
  15992
  
    
    WHEN (`*Accepted_name_rank` = CAST('family' AS varchar(255))) THEN concat_ws(CAST(' ' AS varchar(255)), `Accepted_family__@TNRS__@vegpath.org`, `[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org`)


  		












  

    
      trunk/schemas/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