Project

General

Profile

« Previous | Next » 

Revision 14329

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: taxon_match: added derived column "matched~Name[_no_author]___@TNRS__@vegpath.org", which removes the "No suitable matches found." string

View differences:

schema.sql
222 222 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
223 223 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
224 224 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
225 
	new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;
225 226
226 227 
	-- populate derived cols
227 228 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
......
296 297 
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
297 298 
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
298 299 
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);
299 301
300 302 
	RETURN new;
301 303 
END;
......
429 431 
    scrubbed_unique_taxon_name text,
430 432 
    "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text,
431 433 
    "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text,
434 
    "matched~Name[_no_author]___@TNRS__@vegpath.org" text,
432 435 
    "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text,
433 436 
    matched_has_accepted boolean,
434 437 
    "Accepted_family__@TNRS__@vegpath.org" text,
......
536 539 
    CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),
537 540 
    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
538 541 
    CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
539 
    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL))))
542 
    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
543 
    CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))
540 544 
);
541 545 

  		




  542
  546
  
    

  		




  ......




  612
  616
  
    

  		




  613
  617
  
    

  		




  614
  618
  
    
--


  		




  
  619
  
    
-- Name: COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  620
  
    
--


  		




  
  621
  
    

  		




  
  622
  
    
COMMENT ON COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org" IS '


  		




  
  623
  
    
= NULLIF("*Name_matched", ''No suitable matches found.''::text)


  		




  
  624
  
    

  		




  
  625
  
    
derived column


  		




  
  626
  
    

  		




  
  627
  
    
to modify expr:


  		




  
  628
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col, $$NULLIF("*Name_matched", ''No suitable matches found.''::text)$$)::util.derived_col_def);


  		




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


  		




  
  630
  
    

  		




  
  631
  
    
to rename:


  		




  
  632
  
    
# rename column


  		




  
  633
  
    
# rename CHECK constraint


  		




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


  		




  
  635
  
    
';


  		




  
  636
  
    

  		




  
  637
  
    

  		




  
  638
  
    
--


  		




  615
  639
  
    
-- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  616
  640
  
    
--


  		




  617
  641
  
    

  		












Also available in:  Unified diff