Revision 14283
Added by Aaron Marcuse-Kubitza over 10 years ago
vegbien.sql | ||
---|---|---|
19670 | 19670 |
-- |
19671 | 19671 |
|
19672 | 19672 |
COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
19673 |
derived column; = ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"
|
|
19673 |
= ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched" |
|
19674 | 19674 |
|
19675 |
derived column |
|
19676 |
|
|
19675 | 19677 |
to modify expr: |
19676 | 19678 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"$$)::util.derived_col_def); |
19677 | 19679 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19688 | 19690 |
-- |
19689 | 19691 |
|
19690 | 19692 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
19691 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
|
19693 |
= "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text) |
|
19692 | 19694 |
|
19695 |
derived column |
|
19696 |
|
|
19693 | 19697 |
to modify expr: |
19694 | 19698 |
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); |
19695 | 19699 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19706 | 19710 |
-- |
19707 | 19711 |
|
19708 | 19712 |
COMMENT ON COLUMN taxon_match.matched_has_accepted IS ' |
19709 |
derived column; = "*Accepted_name" IS NOT NULL
|
|
19713 |
= "*Accepted_name" IS NOT NULL |
|
19710 | 19714 |
|
19715 |
derived column |
|
19716 |
|
|
19711 | 19717 |
to modify expr: |
19712 | 19718 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def); |
19713 | 19719 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19724 | 19730 |
-- |
19725 | 19731 |
|
19726 | 19732 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS ' |
19727 |
derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
|
19733 |
= regexp_split_to_array("*Accepted_name_species", '' ''::text) |
|
19728 | 19734 |
|
19735 |
derived column |
|
19736 |
|
|
19729 | 19737 |
to modify expr: |
19730 | 19738 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def); |
19731 | 19739 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19742 | 19750 |
-- |
19743 | 19751 |
|
19744 | 19752 |
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS ' |
19745 |
derived column; = "__accepted_{genus,specific_epithet}"[1]
|
|
19753 |
= "__accepted_{genus,specific_epithet}"[1] |
|
19746 | 19754 |
|
19755 |
derived column |
|
19756 |
|
|
19747 | 19757 |
to modify expr: |
19748 | 19758 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def); |
19749 | 19759 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19760 | 19770 |
-- |
19761 | 19771 |
|
19762 | 19772 |
COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS ' |
19763 |
derived column; = "__accepted_{genus,specific_epithet}"[2]
|
|
19773 |
= "__accepted_{genus,specific_epithet}"[2] |
|
19764 | 19774 |
|
19775 |
derived column |
|
19776 |
|
|
19765 | 19777 |
to modify expr: |
19766 | 19778 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def); |
19767 | 19779 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19778 | 19790 |
-- |
19779 | 19791 |
|
19780 | 19792 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
19781 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
19793 |
= ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text) |
|
19782 | 19794 |
|
19795 |
derived column |
|
19796 |
|
|
19783 | 19797 |
to modify expr: |
19784 | 19798 |
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); |
19785 | 19799 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19796 | 19810 |
-- |
19797 | 19811 |
|
19798 | 19812 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS ' |
19799 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
19813 |
= regexp_split_to_array(__accepted_infraspecific_label, '' ''::text) |
|
19800 | 19814 |
|
19815 |
derived column |
|
19816 |
|
|
19801 | 19817 |
to modify expr: |
19802 | 19818 |
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); |
19803 | 19819 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19814 | 19830 |
-- |
19815 | 19831 |
|
19816 | 19832 |
COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
19817 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[1]
|
|
19833 |
= "__accepted_infraspecific_{rank,epithet}"[1] |
|
19818 | 19834 |
|
19835 |
derived column |
|
19836 |
|
|
19819 | 19837 |
to modify expr: |
19820 | 19838 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def); |
19821 | 19839 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19832 | 19850 |
-- |
19833 | 19851 |
|
19834 | 19852 |
COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
19835 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[2]
|
|
19853 |
= "__accepted_infraspecific_{rank,epithet}"[2] |
|
19836 | 19854 |
|
19855 |
derived column |
|
19856 |
|
|
19837 | 19857 |
to modify expr: |
19838 | 19858 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def); |
19839 | 19859 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19850 | 19870 |
-- |
19851 | 19871 |
|
19852 | 19872 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
19853 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
|
19873 |
= "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text) |
|
19854 | 19874 |
|
19875 |
derived column |
|
19876 |
|
|
19855 | 19877 |
to modify expr: |
19856 | 19878 |
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); |
19857 | 19879 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
19868 | 19890 |
-- |
19869 | 19891 |
|
19870 | 19892 |
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org" IS ' |
19871 |
derived column; = CASE
|
|
19893 |
= CASE |
|
19872 | 19894 |
WHEN matched_has_accepted THEN "*Accepted_name_rank" |
19873 | 19895 |
ELSE "*Name_matched_rank" |
19874 | 19896 |
END |
19875 | 19897 |
|
19898 |
derived column |
|
19899 |
|
|
19876 | 19900 |
to modify expr: |
19877 | 19901 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonRank__@DwC__@vegpath.org'')::util.col, $$CASE |
19878 | 19902 |
WHEN matched_has_accepted THEN "*Accepted_name_rank" |
... | ... | |
19892 | 19916 |
-- |
19893 | 19917 |
|
19894 | 19918 |
COMMENT ON COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
19895 |
derived column; = CASE
|
|
19919 |
= CASE |
|
19896 | 19920 |
WHEN matched_has_accepted THEN "*Accepted_name_family" |
19897 | 19921 |
ELSE "*Name_matched_accepted_family" |
19898 | 19922 |
END |
19899 | 19923 |
|
19924 |
derived column |
|
19925 |
|
|
19900 | 19926 |
to modify expr: |
19901 | 19927 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
19902 | 19928 |
WHEN matched_has_accepted THEN "*Accepted_name_family" |
... | ... | |
19916 | 19942 |
-- |
19917 | 19943 |
|
19918 | 19944 |
COMMENT ON COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org" IS ' |
19919 |
derived column; = CASE
|
|
19945 |
= CASE |
|
19920 | 19946 |
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" |
19921 | 19947 |
ELSE "*Genus_matched" |
19922 | 19948 |
END |
19923 | 19949 |
|
19950 |
derived column |
|
19951 |
|
|
19924 | 19952 |
to modify expr: |
19925 | 19953 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]genus__@DwC__@vegpath.org'')::util.col, $$CASE |
19926 | 19954 |
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" |
... | ... | |
19940 | 19968 |
-- |
19941 | 19969 |
|
19942 | 19970 |
COMMENT ON COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS ' |
19943 |
derived column; = CASE
|
|
19971 |
= CASE |
|
19944 | 19972 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
19945 | 19973 |
ELSE "*Specific_epithet_matched" |
19946 | 19974 |
END |
19947 | 19975 |
|
19976 |
derived column |
|
19977 |
|
|
19948 | 19978 |
to modify expr: |
19949 | 19979 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE |
19950 | 19980 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
... | ... | |
19964 | 19994 |
-- |
19965 | 19995 |
|
19966 | 19996 |
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
19967 |
derived column; = CASE
|
|
19997 |
= CASE |
|
19968 | 19998 |
WHEN matched_has_accepted THEN "*Accepted_name_species" |
19969 | 19999 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
19970 | 20000 |
END |
19971 | 20001 |
|
20002 |
derived column |
|
20003 |
|
|
19972 | 20004 |
to modify expr: |
19973 | 20005 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
19974 | 20006 |
WHEN matched_has_accepted THEN "*Accepted_name_species" |
... | ... | |
19988 | 20020 |
-- |
19989 | 20021 |
|
19990 | 20022 |
COMMENT ON COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
19991 |
derived column; = CASE
|
|
20023 |
= CASE |
|
19992 | 20024 |
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" |
19993 | 20025 |
ELSE "*Infraspecific_rank" |
19994 | 20026 |
END |
19995 | 20027 |
|
20028 |
derived column |
|
20029 |
|
|
19996 | 20030 |
to modify expr: |
19997 | 20031 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$CASE |
19998 | 20032 |
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" |
... | ... | |
20012 | 20046 |
-- |
20013 | 20047 |
|
20014 | 20048 |
COMMENT ON COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
20015 |
derived column; = CASE
|
|
20049 |
= CASE |
|
20016 | 20050 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
20017 | 20051 |
ELSE "*Infraspecific_epithet_matched" |
20018 | 20052 |
END |
20019 | 20053 |
|
20054 |
derived column |
|
20055 |
|
|
20020 | 20056 |
to modify expr: |
20021 | 20057 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE |
20022 | 20058 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
... | ... | |
20036 | 20072 |
-- |
20037 | 20073 |
|
20038 | 20074 |
COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
20039 |
derived column; = CASE
|
|
20075 |
= CASE |
|
20040 | 20076 |
WHEN matched_has_accepted THEN "*Accepted_name" |
20041 | 20077 |
ELSE "*Name_matched" |
20042 | 20078 |
END |
20043 | 20079 |
|
20080 |
derived column |
|
20081 |
|
|
20044 | 20082 |
to modify expr: |
20045 | 20083 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
20046 | 20084 |
WHEN matched_has_accepted THEN "*Accepted_name" |
... | ... | |
20060 | 20098 |
-- |
20061 | 20099 |
|
20062 | 20100 |
COMMENT ON COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
20063 |
derived column; = CASE
|
|
20101 |
= CASE |
|
20064 | 20102 |
WHEN matched_has_accepted THEN "*Accepted_name_author" |
20065 | 20103 |
ELSE "*Name_matched_author" |
20066 | 20104 |
END |
20067 | 20105 |
|
20106 |
derived column |
|
20107 |
|
|
20068 | 20108 |
to modify expr: |
20069 | 20109 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
20070 | 20110 |
WHEN matched_has_accepted THEN "*Accepted_name_author" |
... | ... | |
20084 | 20124 |
-- |
20085 | 20125 |
|
20086 | 20126 |
COMMENT ON COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
20087 |
derived column; = CASE
|
|
20127 |
= CASE |
|
20088 | 20128 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
20089 | 20129 |
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" |
20090 | 20130 |
END |
20091 | 20131 |
|
20132 |
derived column |
|
20133 |
|
|
20092 | 20134 |
to modify expr: |
20093 | 20135 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$CASE |
20094 | 20136 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
Also available in: Unified diff
fix: schemas/util.sql: derived_col_update(): column comment: formula: put on own line to support multiline exprs