Revision 14273
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 461 | 461 |
-- |
| 462 | 462 |
|
| 463 | 463 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 464 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''), '' '')
|
|
| 464 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
| 465 | 465 |
|
| 466 | 466 |
to modify: |
| 467 |
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), ''''), '' '')$$)::util.derived_col_def);
|
|
| 467 |
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);
|
|
| 468 | 468 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 469 | 469 |
'; |
| 470 | 470 |
|
| ... | ... | |
| 474 | 474 |
-- |
| 475 | 475 |
|
| 476 | 476 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 477 |
derived column; = regexp_split_to_array("__accepted_infraspecific_label", '' ''::text)
|
|
| 477 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
| 478 | 478 |
|
| 479 | 479 |
to modify: |
| 480 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array("__accepted_infraspecific_label", '' ''::text)$$)::util.derived_col_def);
|
|
| 480 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
|
| 481 | 481 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 482 | 482 |
'; |
| 483 | 483 |
|
| ... | ... | |
| 513 | 513 |
-- |
| 514 | 514 |
|
| 515 | 515 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 516 |
derived column; = "*Name_matched" || COALESCE('' ''::text || "*Name_matched_author", ''''::text)
|
|
| 516 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
|
| 517 | 517 |
|
| 518 | 518 |
to modify: |
| 519 |
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);
|
|
| 519 |
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);
|
|
| 520 | 520 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 521 | 521 |
'; |
| 522 | 522 |
|
| ... | ... | |
| 526 | 526 |
-- |
| 527 | 527 |
|
| 528 | 528 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 529 |
derived column; = "*Accepted_name" || COALESCE('' ''::text || "*Accepted_name_author", ''''::text)
|
|
| 529 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
|
| 530 | 530 |
|
| 531 | 531 |
to modify: |
| 532 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE('' ''::text || "*Accepted_name_author", ''''::text)$$)::util.derived_col_def);
|
|
| 532 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);
|
|
| 533 | 533 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 534 | 534 |
'; |
| 535 | 535 |
|
| trunk/schemas/util.sql | ||
|---|---|---|
| 1967 | 1967 |
LANGUAGE sql |
| 1968 | 1968 |
AS $$ |
| 1969 | 1969 |
SELECT util.col_add(def); |
| 1970 |
|
|
| 1971 |
-- if table has data, initially disabled because not yet populated |
|
| 1972 |
SELECT util.check_constraint((def.col, util.derived_col_constraint_expr(def)), |
|
| 1973 |
enabled := util.derived_col_constraint_initially_enabled(def)); |
|
| 1974 |
|
|
| 1975 |
-- once CHECK constraint exists so that we can use its canon-ed formula |
|
| 1970 | 1976 |
SELECT util.set_comment(def.col, ' |
| 1971 |
derived column; = '||def.expr||'
|
|
| 1977 |
derived column; = '||util.derived_col_expr(def.col)||'
|
|
| 1972 | 1978 |
|
| 1973 | 1979 |
to modify: |
| 1974 | 1980 |
SELECT util.derived_col_update('||util.quote_typed(def)||');
|
| 1975 | 1981 |
SELECT util.derived_cols_populate('||util.quote_typed((def).col.table_)||');
|
| 1976 | 1982 |
'); |
| 1977 | 1983 |
|
| 1978 |
-- if table has data, initially disabled because not yet populated |
|
| 1979 |
SELECT util.check_constraint((def.col, util.derived_col_constraint_expr(def)), |
|
| 1980 |
enabled := util.derived_col_constraint_initially_enabled(def)); |
|
| 1981 |
|
|
| 1982 | 1984 |
SELECT util.derived_cols_trigger_update((def).col.table_); |
| 1983 | 1985 |
$$; |
| 1984 | 1986 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19573 | 19573 |
-- |
| 19574 | 19574 |
|
| 19575 | 19575 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 19576 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''), '' '')
|
|
| 19576 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
| 19577 | 19577 |
|
| 19578 | 19578 |
to modify: |
| 19579 |
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), ''''), '' '')$$)::util.derived_col_def);
|
|
| 19579 |
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);
|
|
| 19580 | 19580 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19581 | 19581 |
'; |
| 19582 | 19582 |
|
| ... | ... | |
| 19586 | 19586 |
-- |
| 19587 | 19587 |
|
| 19588 | 19588 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 19589 |
derived column; = regexp_split_to_array("__accepted_infraspecific_label", '' ''::text)
|
|
| 19589 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
| 19590 | 19590 |
|
| 19591 | 19591 |
to modify: |
| 19592 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array("__accepted_infraspecific_label", '' ''::text)$$)::util.derived_col_def);
|
|
| 19592 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
|
| 19593 | 19593 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19594 | 19594 |
'; |
| 19595 | 19595 |
|
| ... | ... | |
| 19625 | 19625 |
-- |
| 19626 | 19626 |
|
| 19627 | 19627 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 19628 |
derived column; = "*Name_matched" || COALESCE('' ''::text || "*Name_matched_author", ''''::text)
|
|
| 19628 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
|
| 19629 | 19629 |
|
| 19630 | 19630 |
to modify: |
| 19631 |
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);
|
|
| 19631 |
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);
|
|
| 19632 | 19632 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19633 | 19633 |
'; |
| 19634 | 19634 |
|
| ... | ... | |
| 19638 | 19638 |
-- |
| 19639 | 19639 |
|
| 19640 | 19640 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 19641 |
derived column; = "*Accepted_name" || COALESCE('' ''::text || "*Accepted_name_author", ''''::text)
|
|
| 19641 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
|
| 19642 | 19642 |
|
| 19643 | 19643 |
to modify: |
| 19644 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE('' ''::text || "*Accepted_name_author", ''''::text)$$)::util.derived_col_def);
|
|
| 19644 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);
|
|
| 19645 | 19645 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19646 | 19646 |
'; |
| 19647 | 19647 |
|
Also available in: Unified diff
bugfix: schemas/util.sql: derived_col_update(): don't set_comment() until CHECK constraint exists so that we can use its canon-ed formula