Project

General

Profile

« Previous | Next » 

Revision 14347

Added by Aaron Marcuse-Kubitza over 10 years ago

bugfix: inputs/.TNRS/schema.sql: taxon_match: use wrapper for util.remove_prefix() so CHECK constraints that use it don't get dropped when the util schema is reinstalled

View differences:

trunk/inputs/.TNRS/schema.sql
93 93 

  		




  94
  94
  
    

  		




  95
  95
  
    
--


  		




  
  96
  
    
-- Name: remove_prefix(text, text, boolean); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  
  97
  
    
--


  		




  
  98
  
    

  		




  
  99
  
    
CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true) RETURNS text


  		




  
  100
  
    
    LANGUAGE sql IMMUTABLE


  		




  
  101
  
    
    AS $$


  		




  
  102
  
    
SELECT util.remove_prefix(prefix, str, require)


  		




  
  103
  
    
$$;


  		




  
  104
  
    

  		




  
  105
  
    

  		




  
  106
  
    
--


  		




  
  107
  
    
-- Name: FUNCTION remove_prefix(prefix text, str text, require boolean); Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  108
  
    
--


  		




  
  109
  
    

  		




  
  110
  
    
COMMENT ON FUNCTION remove_prefix(prefix text, str text, require boolean) IS '


  		




  
  111
  
    
wrapper that prevents views from getting dropped when the util schema is reinstalled


  		




  
  112
  
    
';


  		




  
  113
  
    

  		




  
  114
  
    

  		




  
  115
  
    
--


  		




  96
  116
  
    
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  97
  117
  
    
--


  		




  98
  118
  
    

  		




  ......




  247
  267
  
    
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  248
  268
  
    
    ELSE "*Accepted_name_species"


  		




  249
  269
  
    
END FROM (SELECT new.*) new);


  		




  250
  
  
    
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);


  		




  
  270
  
    
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);


  		




  251
  271
  
    
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);


  		




  252
  272
  
    
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);


  		




  253
  273
  
    
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);


  		




  ......




  536
  556
  
    
    ELSE "*Name_matched_rank"


  		




  537
  557
  
    
END))),


  		




  538
  558
  
    
    CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),


  		




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


  		




  
  559
  
    
    CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),


  		




  540
  560
  
    
    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),


  		




  541
  561
  
    
    CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),


  		




  542
  562
  
    
    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),


  		




  ......




  808
  828
  
    
--


  		




  809
  829
  
    

  		




  810
  830
  
    
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS '


  		




  811
  
  
    
= ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)


  		




  
  831
  
    
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)


  		




  812
  832
  
    

  		




  813
  833
  
    
derived column


  		




  814
  834
  
    

  		




  815
  835
  
    
to modify expr:


  		




  816
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);


  		




  
  836
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);


  		




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


  		




  818
  838
  
    

  		




  819
  839
  
    
to rename:


  		












  

    
      trunk/schemas/vegbien.my.sql
    
  





  15806
  15806
  
    

  		




  15807
  15807
  
    

  		




  15808
  15808
  
    
--


  		




  
  15809
  
    
-- Name: remove_prefix(varchar(255), varchar(255), int(1)); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  
  15810
  
    
--


  		




  
  15811
  
    

  		




  
  15812
  
    

  		




  
  15813
  
    

  		




  
  15814
  
    

  		




  
  15815
  
    
--


  		




  
  15816
  
    
-- Name: FUNCTION remove_prefix(prefix varchar(255), str varchar(255), require int(1)); Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  15817
  
    
--


  		




  
  15818
  
    

  		




  
  15819
  
    

  		




  
  15820
  
    

  		




  
  15821
  
    

  		




  
  15822
  
    
--


  		




  15809
  15823
  
    
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  15810
  15824
  
    
--


  		




  15811
  15825
  
    

  		












  

    
      trunk/schemas/vegbien.sql
    
  





  19712
  19712
  
    

  		




  19713
  19713
  
    

  		




  19714
  19714
  
    
--


  		




  
  19715
  
    
-- Name: remove_prefix(text, text, boolean); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  
  19716
  
    
--


  		




  
  19717
  
    

  		




  
  19718
  
    
CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true) RETURNS text


  		




  
  19719
  
    
    LANGUAGE sql IMMUTABLE


  		




  
  19720
  
    
    AS $$


  		




  
  19721
  
    
SELECT util.remove_prefix(prefix, str, require)


  		




  
  19722
  
    
$$;


  		




  
  19723
  
    

  		




  
  19724
  
    

  		




  
  19725
  
    
--


  		




  
  19726
  
    
-- Name: FUNCTION remove_prefix(prefix text, str text, require boolean); Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  19727
  
    
--


  		




  
  19728
  
    

  		




  
  19729
  
    
COMMENT ON FUNCTION remove_prefix(prefix text, str text, require boolean) IS '


  		




  
  19730
  
    
wrapper that prevents views from getting dropped when the util schema is reinstalled


  		




  
  19731
  
    
';


  		




  
  19732
  
    

  		




  
  19733
  
    

  		




  
  19734
  
    
--


  		




  19715
  19735
  
    
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -


  		




  19716
  19736
  
    
--


  		




  19717
  19737
  
    

  		




  ......




  19866
  19886
  
    
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  19867
  19887
  
    
    ELSE "*Accepted_name_species"


  		




  19868
  19888
  
    
END FROM (SELECT new.*) new);


  		




  19869
  
  
    
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);


  		




  
  19889
  
    
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);


  		




  19870
  19890
  
    
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);


  		




  19871
  19891
  
    
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);


  		




  19872
  19892
  
    
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);


  		




  ......




  20197
  20217
  
    
    ELSE "*Name_matched_rank"


  		




  20198
  20218
  
    
END))),


  		




  20199
  20219
  
    
    CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),


  		




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


  		




  
  20220
  
    
    CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)))),


  		




  20201
  20221
  
    
    CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),


  		




  20202
  20222
  
    
    CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),


  		




  20203
  20223
  
    
    CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),


  		




  ......




  20469
  20489
  
    
--


  		




  20470
  20490
  
    

  		




  20471
  20491
  
    
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS '


  		




  20472
  
  
    
= ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)


  		




  
  20492
  
    
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)


  		




  20473
  20493
  
    

  		




  20474
  20494
  
    
derived column


  		




  20475
  20495
  
    

  		




  20476
  20496
  
    
to modify expr:


  		




  20477
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);


  		




  
  20497
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);


  		




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


  		




  20479
  20499
  
    

  		




  20480
  20500
  
    
to rename:


  		












Also available in:  Unified diff