Project

General

Profile

« Previous | Next » 

Revision 14330

Added by Aaron Marcuse-Kubitza over 10 years ago

bugfix: inputs/.TNRS/schema.sql: taxon_match: derived columns: use new "matched~Name[_no_author]___@TNRS__@vegpath.org" instead of "*Name_matched" so that "No suitable matches found." is removed before concatenating with other fields

View differences:

trunk/inputs/.TNRS/data.sql
55 55 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 18, 4, 'Fabaceae unknown #2', 0.67000000000000004, 'Ficaceae', 'family', 0.770000000000000018, 'Bercht. & J. Presl', 'http://www.tropicos.org/Name/100353631', NULL, NULL, 'Ficaceae', 0.75, 'Ficaceae', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'unknown #2', 'No opinion', NULL, NULL, NULL, NULL, NULL, NULL, 'false', 'tropicos', ' ', NULL, false, 'Ficaceae Bercht. & J. Presl', 'unknown #2', NULL, 'Ficaceae', 'Ficaceae Bercht. & J. Presl', false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'no opinion', 'family', 'Ficaceae', NULL, NULL, NULL, 'Ficaceae unknown #2', NULL, NULL, 'Ficaceae', 'Bercht. & J. Presl', 'Ficaceae Bercht. & J. Presl');
56 56 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 19, 4, 'Fabaceae unknown #2', 0.67000000000000004, 'Fucaceae', 'family', 0.770000000000000018, NULL, 'http://www.tropicos.org/Name/100371040', NULL, NULL, 'Fucaceae', 0.75, 'Fucaceae', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'unknown #2', 'No opinion', NULL, NULL, NULL, NULL, NULL, NULL, 'false', 'tropicos', ' ', NULL, false, 'Fucaceae', 'unknown #2', NULL, 'Fucaceae', 'Fucaceae', false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'no opinion', 'family', 'Fucaceae', NULL, NULL, NULL, 'Fucaceae unknown #2', NULL, NULL, 'Fucaceae', NULL, 'Fucaceae');
57 57 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 20, 4, 'Fabaceae unknown #2', 0.900000000000000022, 'Fabaceae', 'family', 1, NULL, 'http://plants.usda.gov/java/nameSearch', NULL, NULL, 'Fabaceae', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'unknown #2', 'Accepted', 'Fabaceae', NULL, 'family', 'http://plants.usda.gov/java/nameSearch', NULL, NULL, 'false', 'usda', ' ', NULL, true, 'Fabaceae', 'unknown #2', NULL, 'Fabaceae', 'Fabaceae', true, 'Fabaceae', NULL, NULL, NULL, 'Fabaceae unknown #2', NULL, NULL, NULL, NULL, 'Fabaceae', 'accepted', 'family', 'Fabaceae', NULL, NULL, NULL, 'Fabaceae unknown #2', NULL, NULL, 'Fabaceae', NULL, 'Fabaceae');
58 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 21, 5, 'Fam_indet. Boyle#6501', 0, 'No suitable matches found.', NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, 0, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'true', NULL, ' ', NULL, false, NULL, NULL, NULL, NULL, 'No suitable matches found.', false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'No suitable matches found.', NULL, 'No suitable matches found.');
58 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 21, 5, 'Fam_indet. Boyle#6501', 0, 'No suitable matches found.', NULL, 0, NULL, NULL, NULL, 0, NULL, 0, NULL, NULL, 0, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'true', NULL, ' ', NULL, false, NULL, NULL, NULL, NULL, NULL, false, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
59 59 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 22, 6, 'Poa annua', 1, 'Poa annua', 'species', 1, 'L.', 'http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN', NULL, NULL, NULL, NULL, 'Poaceae', 'Poa', 1, 'annua', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Accepted', 'Poa annua', 'L.', 'species', 'http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN', 'Poa annua', 'Poaceae', 'true', 'tpl;tropicos;usda', ' ', NULL, true, 'Poaceae Poa annua L.', NULL, 'Poa annua', 'Poa annua', 'Poa annua L.', true, 'Poaceae', '{Poa,annua}', 'Poa', 'annua', 'Poa annua', NULL, NULL, NULL, NULL, 'Poa annua L.', 'accepted', 'species', 'Poaceae', 'Poa', 'annua', 'Poa annua', 'Poa annua', NULL, 'annua', 'Poa annua', 'L.', 'Poa annua L.');
60 60 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 23, 6, 'Poa annua', 1, 'Poa annua', 'species', 1, 'Cham. & Schltdl.', 'http://www.theplantlist.org/tpl1.1/record/kew-435195', NULL, NULL, NULL, NULL, 'Poaceae', 'Poa', 1, 'annua', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Synonym', 'Poa infirma', 'Kunth', 'species', 'http://www.theplantlist.org/tpl1.1/record/kew-436189', 'Poa infirma', 'Poaceae', 'false', 'tpl', ' ', NULL, true, 'Poaceae Poa infirma Kunth', NULL, 'Poa annua', 'Poa annua', 'Poa annua Cham. & Schltdl.', true, 'Poaceae', '{Poa,infirma}', 'Poa', 'infirma', 'Poa infirma', NULL, NULL, NULL, NULL, 'Poa infirma Kunth', 'accepted', 'species', 'Poaceae', 'Poa', 'infirma', 'Poa infirma', 'Poa infirma', NULL, 'infirma', 'Poa infirma', 'Kunth', 'Poa infirma Kunth');
61 61 
INSERT INTO taxon_match VALUES ('2014-07-25 12:11:56.765269-07', 24, 7, 'Poa annua L.', 1, 'Poa annua', 'species', 1, 'L.', 'http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN', 'L.', 1, NULL, NULL, 'Poaceae', 'Poa', 1, 'annua', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Accepted', 'Poa annua', 'L.', 'species', 'http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN', 'Poa annua', 'Poaceae', 'true', 'tpl;tropicos;usda', ' ', NULL, true, 'Poaceae Poa annua L.', NULL, 'Poa annua', 'Poa annua', 'Poa annua L.', true, 'Poaceae', '{Poa,annua}', 'Poa', 'annua', 'Poa annua', NULL, NULL, NULL, NULL, 'Poa annua L.', 'accepted', 'species', 'Poaceae', 'Poa', 'annua', 'Poa annua', 'Poa annua', NULL, 'annua', 'Poa annua', 'L.', 'Poa annua L.');
trunk/inputs/.TNRS/schema.sql
198 198 
	-- clear derived cols so old values won't be used in calculations
199 199 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;
200 200 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
201 
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;
201 202 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
202 203 
	new.matched_has_accepted = NULL;
203 204 
	new."Accepted_family__@TNRS__@vegpath.org" = NULL;
......
222 223 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
223 224 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
224 225 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
225 
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;
226 226
227 227 
	-- populate derived cols
228 228 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
229 229 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
230 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
230 
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);
231 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
231 232 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
232 233 
	new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family",
233 234 
CASE
......
287 288 
END FROM (SELECT new.*) new);
288 289 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
289 290 
    WHEN matched_has_accepted THEN "*Accepted_name"
290 
    ELSE "*Name_matched"
291 
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
291 292 
END FROM (SELECT new.*) new);
292 293 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
293 294 
    WHEN matched_has_accepted THEN "*Accepted_name_author"
......
297 298 
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
298 299 
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
299 300 
END FROM (SELECT new.*) new);
300 
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);
301 301
302 302 
	RETURN new;
303 303 
END;
......
476 476 
END))),
477 477 
    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))))),
478 478 
    CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
479 
    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))))),
479 
    CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))),
480 480 
    CONSTRAINT "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM (("*Genus_matched" || ' '::text) || "*Specific_epithet_matched")))),
481 481 
    CONSTRAINT "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" CHECK ((NOT ("[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS DISTINCT FROM "*Unmatched_terms"))),
482 482 
    CONSTRAINT "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM
......
513 513 
    CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
514 514 
CASE
515 515 
    WHEN matched_has_accepted THEN "*Accepted_name"
516 
    ELSE "*Name_matched"
516 
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
517 517 
END))),
518 518 
    CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM
519 519 
CASE
......
640 640 
--
641 641 

  		




  642
  642
  
    
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS '


  		




  643
  
  
    
= "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)


  		




  
  643
  
    
= "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)


  		




  644
  644
  
    

  		




  645
  645
  
    
derived column


  		




  646
  646
  
    

  		




  647
  647
  
    
to modify expr:


  		




  648
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);


  		




  
  648
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);


  		




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


  		




  650
  650
  
    

  		




  651
  651
  
    
to rename:


  		




  ......




  1136
  1136
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS '


  		




  1137
  1137
  
    
= CASE


  		




  1138
  1138
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  1139
  
  
    
    ELSE "*Name_matched"


  		




  
  1139
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




  1140
  1140
  
    
END


  		




  1141
  1141
  
    

  		




  1142
  1142
  
    
derived column


  		




  ......




  1144
  1144
  
    
to modify expr:


  		




  1145
  1145
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE


  		




  1146
  1146
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  1147
  
  
    
    ELSE "*Name_matched"


  		




  
  1147
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




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


  		




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


  		




  1150
  1150
  
    

  		












  

    
      trunk/schemas/vegbien.my.sql
    
  





  15522
  15522
  
    
END)))


  		




  15523
  15523
  
    
CASE


  		




  15524
  15524
  
    
    WHEN matched_has_accepted THEN `*Accepted_name`


  		




  15525
  
  
    
    ELSE `*Name_matched`


  		




  
  15525
  
    
    ELSE `matched~Name[_no_author]___@TNRS__@vegpath.org`


  		




  15526
  15526
  
    
END)))


  		




  15527
  15527
  
    
CASE


  		




  15528
  15528
  
    
    WHEN matched_has_accepted THEN `[accepted_]scientificName[_with_author]__@DwC__@vegpath.org`


  		












  

    
      trunk/schemas/vegbien.sql
    
  





  19260
  19260
  
    
	-- clear derived cols so old values won't be used in calculations


  		




  19261
  19261
  
    
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;


  		




  19262
  19262
  
    
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;


  		




  
  19263
  
    
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;


  		




  19263
  19264
  
    
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;


  		




  19264
  19265
  
    
	new.matched_has_accepted = NULL;


  		




  19265
  19266
  
    
	new."Accepted_family__@TNRS__@vegpath.org" = NULL;


  		




  ......




  19284
  19285
  
    
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;


  		




  19285
  19286
  
    
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;


  		




  19286
  19287
  
    
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;


  		




  19287
  
  
    
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;


  		




  19288
  19288
  
    
	


  		




  19289
  19289
  
    
	-- populate derived cols


  		




  19290
  19290
  
    
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);


  		




  19291
  19291
  
    
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);


  		




  19292
  
  
    
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);


  		




  
  19292
  
    
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);


  		




  
  19293
  
    
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);


  		




  19293
  19294
  
    
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);


  		




  19294
  19295
  
    
	new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family",


  		




  19295
  19296
  
    
CASE


  		




  ......




  19349
  19350
  
    
END FROM (SELECT new.*) new);


  		




  19350
  19351
  
    
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE


  		




  19351
  19352
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  19352
  
  
    
    ELSE "*Name_matched"


  		




  
  19353
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




  19353
  19354
  
    
END FROM (SELECT new.*) new);


  		




  19354
  19355
  
    
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE


  		




  19355
  19356
  
    
    WHEN matched_has_accepted THEN "*Accepted_name_author"


  		




  ......




  19359
  19360
  
    
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"


  		




  19360
  19361
  
    
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"


  		




  19361
  19362
  
    
END FROM (SELECT new.*) new);


  		




  19362
  
  
    
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);


  		




  19363
  19363
  
    
	


  		




  19364
  19364
  
    
	RETURN new;


  		




  19365
  19365
  
    
END;


  		




  ......




  19580
  19580
  
    
END))),


  		




  19581
  19581
  
    
    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))))),


  		




  19582
  19582
  
    
    CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),


  		




  19583
  
  
    
    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))))),


  		




  
  19583
  
    
    CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))),


  		




  19584
  19584
  
    
    CONSTRAINT "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM (("*Genus_matched" || ' '::text) || "*Specific_epithet_matched")))),


  		




  19585
  19585
  
    
    CONSTRAINT "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" CHECK ((NOT ("[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS DISTINCT FROM "*Unmatched_terms"))),


  		




  19586
  19586
  
    
    CONSTRAINT "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM


  		




  ......




  19617
  19617
  
    
    CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM


  		




  19618
  19618
  
    
CASE


  		




  19619
  19619
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  19620
  
  
    
    ELSE "*Name_matched"


  		




  
  19620
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




  19621
  19621
  
    
END))),


  		




  19622
  19622
  
    
    CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM


  		




  19623
  19623
  
    
CASE


  		




  ......




  19744
  19744
  
    
--


  		




  19745
  19745
  
    

  		




  19746
  19746
  
    
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS '


  		




  19747
  
  
    
= "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)


  		




  
  19747
  
    
= "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)


  		




  19748
  19748
  
    

  		




  19749
  19749
  
    
derived column


  		




  19750
  19750
  
    

  		




  19751
  19751
  
    
to modify expr:


  		




  19752
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);


  		




  
  19752
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);


  		




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


  		




  19754
  19754
  
    

  		




  19755
  19755
  
    
to rename:


  		




  ......




  20240
  20240
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS '


  		




  20241
  20241
  
    
= CASE


  		




  20242
  20242
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  20243
  
  
    
    ELSE "*Name_matched"


  		




  
  20243
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




  20244
  20244
  
    
END


  		




  20245
  20245
  
    

  		




  20246
  20246
  
    
derived column


  		




  ......




  20248
  20248
  
    
to modify expr:


  		




  20249
  20249
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE


  		




  20250
  20250
  
    
    WHEN matched_has_accepted THEN "*Accepted_name"


  		




  20251
  
  
    
    ELSE "*Name_matched"


  		




  
  20251
  
    
    ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"


  		




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


  		




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


  		




  20254
  20254
  
    

  		












Also available in:  Unified diff