Project

General

Profile

« Previous | Next » 

Revision 14328

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: reordered derived columns in dependency order

View differences:

schema.sql
210 210 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
211 211 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
212 212 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
213 
	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL;
213 214 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL;
214 215 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
215 216 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL;
216 217 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL;
217 218 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
219 
	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
218 220 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
219 221 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
220 222 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
221 223 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
222 224 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
223 
	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL;
224 
	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
225 225
226 226 
	-- populate derived cols
227 227 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
......
250 250 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
251 251 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
252 252 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
253 
	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new);
253 254 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE
254 255 
    WHEN matched_has_accepted THEN "*Accepted_name_rank"
255 256 
    ELSE "*Name_matched_rank"
......
270 271 
    WHEN matched_has_accepted THEN "*Accepted_name_species"
271 272 
    ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
272 273 
END FROM (SELECT new.*) new);
274 
	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
275 
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
276 
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
277 
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
278 
END FROM (SELECT new.*) new);
273 279 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT CASE
274 280 
    WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
275 281 
    ELSE "*Infraspecific_rank"
......
290 296 
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
291 297 
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
292 298 
END FROM (SELECT new.*) new);
293 
	new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new);
294 
	new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
295 
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
296 
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
297 
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
298 
END FROM (SELECT new.*) new);
299 299
300 300 
	RETURN new;
301 301 
END;
......
441 441 
    "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
442 442 
    "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" text,
443 443 
    "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" text,
444 
    "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text,
444 445 
    "[scrubbed_]taxonRank__@DwC__@vegpath.org" text,
445 446 
    "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" text,
446 447 
    "[scrubbed_]genus__@DwC__@vegpath.org" text,
447 448 
    "[scrubbed_]specificEpithet__@DwC__@vegpath.org" text,
448 449 
    "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" text,
450 
    "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text,
449 451 
    "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
450 452 
    "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" text,
451 453 
    "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text,
452 454 
    "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text,
453 455 
    "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text,
454 
    "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text,
455 
    "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text,
456 456 
    CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family",
457 457 
CASE
458 458 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
......
876 876 

  		




  877
  877
  
    

  		




  878
  878
  
    
--


  		




  
  879
  
    
-- Name: COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  880
  
    
--


  		




  
  881
  
    

  		




  
  882
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS '


  		




  
  883
  
    
= "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")


  		




  
  884
  
    

  		




  
  885
  
    
derived column


  		




  
  886
  
    

  		




  
  887
  
    
to modify expr:


  		




  
  888
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonomicStatus__@DwC__@vegpath.org'')::util.col, $$"TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")$$)::util.derived_col_def);


  		




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


  		




  
  890
  
    

  		




  
  891
  
    
to rename:


  		




  
  892
  
    
# rename column


  		




  
  893
  
    
# rename CHECK constraint


  		




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


  		




  
  895
  
    
';


  		




  
  896
  
    

  		




  
  897
  
    

  		




  
  898
  
    
--


  		




  879
  899
  
    
-- Name: COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  880
  900
  
    
--


  		




  881
  901
  
    

  		




  ......




  1006
  1026
  
    

  		




  1007
  1027
  
    

  		




  1008
  1028
  
    
--


  		




  
  1029
  
    
-- Name: COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  
  1030
  
    
--


  		




  
  1031
  
    

  		




  
  1032
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '


  		




  
  1033
  
    
= CASE


  		




  
  1034
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1035
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1036
  
    
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"


  		




  
  1037
  
    
END


  		




  
  1038
  
    

  		




  
  1039
  
    
derived column


  		




  
  1040
  
    

  		




  
  1041
  
    
to modify expr:


  		




  
  1042
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE


  		




  
  1043
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1044
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  
  1045
  
    
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"


  		




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


  		




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


  		




  
  1048
  
    

  		




  
  1049
  
    
to rename:


  		




  
  1050
  
    
# rename column


  		




  
  1051
  
    
# rename CHECK constraint


  		




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


  		




  
  1053
  
    
';


  		




  
  1054
  
    

  		




  
  1055
  
    

  		




  
  1056
  
    
--


  		




  1009
  1057
  
    
-- Name: COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  1010
  1058
  
    
--


  		




  1011
  1059
  
    

  		




  ......




  1136
  1184
  
    

  		




  1137
  1185
  
    

  		




  1138
  1186
  
    
--


  		




  1139
  
  
    
-- Name: COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  1140
  
  
    
--


  		




  1141
  
  
    

  		




  1142
  
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS '


  		




  1143
  
  
    
= "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")


  		




  1144
  
  
    

  		




  1145
  
  
    
derived column


  		




  1146
  
  
    

  		




  1147
  
  
    
to modify expr:


  		




  1148
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonomicStatus__@DwC__@vegpath.org'')::util.col, $$"TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")$$)::util.derived_col_def);


  		




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


  		




  1150
  
  
    

  		




  1151
  
  
    
to rename:


  		




  1152
  
  
    
# rename column


  		




  1153
  
  
    
# rename CHECK constraint


  		




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


  		




  1155
  
  
    
';


  		




  1156
  
  
    

  		




  1157
  
  
    

  		




  1158
  
  
    
--


  		




  1159
  
  
    
-- Name: COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: -


  		




  1160
  
  
    
--


  		




  1161
  
  
    

  		




  1162
  
  
    
COMMENT ON COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '


  		




  1163
  
  
    
= CASE


  		




  1164
  
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  1165
  
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  1166
  
  
    
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"


  		




  1167
  
  
    
END


  		




  1168
  
  
    

  		




  1169
  
  
    
derived column


  		




  1170
  
  
    

  		




  1171
  
  
    
to modify expr:


  		




  1172
  
  
    
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE


  		




  1173
  
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  1174
  
  
    
    WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")


  		




  1175
  
  
    
    ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"


  		




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


  		




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


  		




  1178
  
  
    

  		




  1179
  
  
    
to rename:


  		




  1180
  
  
    
# rename column


  		




  1181
  
  
    
# rename CHECK constraint


  		




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


  		




  1183
  
  
    
';


  		




  1184
  
  
    

  		




  1185
  
  
    

  		




  1186
  
  
    
--


  		




  1187
  1187
  
    
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: -


  		




  1188
  1188
  
    
--


  		




  1189
  1189
  
    

  		












Also available in:  Unified diff