Revision 14283
Added by Aaron Marcuse-Kubitza over 10 years ago
trunk/inputs/.TNRS/schema.sql | ||
---|---|---|
558 | 558 |
-- |
559 | 559 |
|
560 | 560 |
COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
561 |
derived column; = ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"
|
|
561 |
= ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched" |
|
562 | 562 |
|
563 |
derived column |
|
564 |
|
|
563 | 565 |
to modify expr: |
564 | 566 |
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); |
565 | 567 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
576 | 578 |
-- |
577 | 579 |
|
578 | 580 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
579 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
|
581 |
= "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text) |
|
580 | 582 |
|
583 |
derived column |
|
584 |
|
|
581 | 585 |
to modify expr: |
582 | 586 |
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); |
583 | 587 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
594 | 598 |
-- |
595 | 599 |
|
596 | 600 |
COMMENT ON COLUMN taxon_match.matched_has_accepted IS ' |
597 |
derived column; = "*Accepted_name" IS NOT NULL
|
|
601 |
= "*Accepted_name" IS NOT NULL |
|
598 | 602 |
|
603 |
derived column |
|
604 |
|
|
599 | 605 |
to modify expr: |
600 | 606 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def); |
601 | 607 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
612 | 618 |
-- |
613 | 619 |
|
614 | 620 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS ' |
615 |
derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
|
621 |
= regexp_split_to_array("*Accepted_name_species", '' ''::text) |
|
616 | 622 |
|
623 |
derived column |
|
624 |
|
|
617 | 625 |
to modify expr: |
618 | 626 |
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); |
619 | 627 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
630 | 638 |
-- |
631 | 639 |
|
632 | 640 |
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS ' |
633 |
derived column; = "__accepted_{genus,specific_epithet}"[1]
|
|
641 |
= "__accepted_{genus,specific_epithet}"[1] |
|
634 | 642 |
|
643 |
derived column |
|
644 |
|
|
635 | 645 |
to modify expr: |
636 | 646 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def); |
637 | 647 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
648 | 658 |
-- |
649 | 659 |
|
650 | 660 |
COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS ' |
651 |
derived column; = "__accepted_{genus,specific_epithet}"[2]
|
|
661 |
= "__accepted_{genus,specific_epithet}"[2] |
|
652 | 662 |
|
663 |
derived column |
|
664 |
|
|
653 | 665 |
to modify expr: |
654 | 666 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def); |
655 | 667 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
666 | 678 |
-- |
667 | 679 |
|
668 | 680 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
669 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
|
681 |
= ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text) |
|
670 | 682 |
|
683 |
derived column |
|
684 |
|
|
671 | 685 |
to modify expr: |
672 | 686 |
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); |
673 | 687 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
684 | 698 |
-- |
685 | 699 |
|
686 | 700 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS ' |
687 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
|
701 |
= regexp_split_to_array(__accepted_infraspecific_label, '' ''::text) |
|
688 | 702 |
|
703 |
derived column |
|
704 |
|
|
689 | 705 |
to modify expr: |
690 | 706 |
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); |
691 | 707 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
702 | 718 |
-- |
703 | 719 |
|
704 | 720 |
COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
705 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[1]
|
|
721 |
= "__accepted_infraspecific_{rank,epithet}"[1] |
|
706 | 722 |
|
723 |
derived column |
|
724 |
|
|
707 | 725 |
to modify expr: |
708 | 726 |
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); |
709 | 727 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
720 | 738 |
-- |
721 | 739 |
|
722 | 740 |
COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
723 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[2]
|
|
741 |
= "__accepted_infraspecific_{rank,epithet}"[2] |
|
724 | 742 |
|
743 |
derived column |
|
744 |
|
|
725 | 745 |
to modify expr: |
726 | 746 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def); |
727 | 747 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
738 | 758 |
-- |
739 | 759 |
|
740 | 760 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
741 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
|
761 |
= "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text) |
|
742 | 762 |
|
763 |
derived column |
|
764 |
|
|
743 | 765 |
to modify expr: |
744 | 766 |
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); |
745 | 767 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass); |
... | ... | |
756 | 778 |
-- |
757 | 779 |
|
758 | 780 |
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org" IS ' |
759 |
derived column; = CASE
|
|
781 |
= CASE |
|
760 | 782 |
WHEN matched_has_accepted THEN "*Accepted_name_rank" |
761 | 783 |
ELSE "*Name_matched_rank" |
762 | 784 |
END |
763 | 785 |
|
786 |
derived column |
|
787 |
|
|
764 | 788 |
to modify expr: |
765 | 789 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonRank__@DwC__@vegpath.org'')::util.col, $$CASE |
766 | 790 |
WHEN matched_has_accepted THEN "*Accepted_name_rank" |
... | ... | |
780 | 804 |
-- |
781 | 805 |
|
782 | 806 |
COMMENT ON COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
783 |
derived column; = CASE
|
|
807 |
= CASE |
|
784 | 808 |
WHEN matched_has_accepted THEN "*Accepted_name_family" |
785 | 809 |
ELSE "*Name_matched_accepted_family" |
786 | 810 |
END |
787 | 811 |
|
812 |
derived column |
|
813 |
|
|
788 | 814 |
to modify expr: |
789 | 815 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
790 | 816 |
WHEN matched_has_accepted THEN "*Accepted_name_family" |
... | ... | |
804 | 830 |
-- |
805 | 831 |
|
806 | 832 |
COMMENT ON COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org" IS ' |
807 |
derived column; = CASE
|
|
833 |
= CASE |
|
808 | 834 |
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" |
809 | 835 |
ELSE "*Genus_matched" |
810 | 836 |
END |
811 | 837 |
|
838 |
derived column |
|
839 |
|
|
812 | 840 |
to modify expr: |
813 | 841 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]genus__@DwC__@vegpath.org'')::util.col, $$CASE |
814 | 842 |
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org" |
... | ... | |
828 | 856 |
-- |
829 | 857 |
|
830 | 858 |
COMMENT ON COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS ' |
831 |
derived column; = CASE
|
|
859 |
= CASE |
|
832 | 860 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
833 | 861 |
ELSE "*Specific_epithet_matched" |
834 | 862 |
END |
835 | 863 |
|
864 |
derived column |
|
865 |
|
|
836 | 866 |
to modify expr: |
837 | 867 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE |
838 | 868 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
... | ... | |
852 | 882 |
-- |
853 | 883 |
|
854 | 884 |
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
855 |
derived column; = CASE
|
|
885 |
= CASE |
|
856 | 886 |
WHEN matched_has_accepted THEN "*Accepted_name_species" |
857 | 887 |
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" |
858 | 888 |
END |
859 | 889 |
|
890 |
derived column |
|
891 |
|
|
860 | 892 |
to modify expr: |
861 | 893 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
862 | 894 |
WHEN matched_has_accepted THEN "*Accepted_name_species" |
... | ... | |
876 | 908 |
-- |
877 | 909 |
|
878 | 910 |
COMMENT ON COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
879 |
derived column; = CASE
|
|
911 |
= CASE |
|
880 | 912 |
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" |
881 | 913 |
ELSE "*Infraspecific_rank" |
882 | 914 |
END |
883 | 915 |
|
916 |
derived column |
|
917 |
|
|
884 | 918 |
to modify expr: |
885 | 919 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$CASE |
886 | 920 |
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" |
... | ... | |
900 | 934 |
-- |
901 | 935 |
|
902 | 936 |
COMMENT ON COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
903 |
derived column; = CASE
|
|
937 |
= CASE |
|
904 | 938 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
905 | 939 |
ELSE "*Infraspecific_epithet_matched" |
906 | 940 |
END |
907 | 941 |
|
942 |
derived column |
|
943 |
|
|
908 | 944 |
to modify expr: |
909 | 945 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE |
910 | 946 |
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org" |
... | ... | |
924 | 960 |
-- |
925 | 961 |
|
926 | 962 |
COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
927 |
derived column; = CASE
|
|
963 |
= CASE |
|
928 | 964 |
WHEN matched_has_accepted THEN "*Accepted_name" |
929 | 965 |
ELSE "*Name_matched" |
930 | 966 |
END |
931 | 967 |
|
968 |
derived column |
|
969 |
|
|
932 | 970 |
to modify expr: |
933 | 971 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
934 | 972 |
WHEN matched_has_accepted THEN "*Accepted_name" |
... | ... | |
948 | 986 |
-- |
949 | 987 |
|
950 | 988 |
COMMENT ON COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS ' |
951 |
derived column; = CASE
|
|
989 |
= CASE |
|
952 | 990 |
WHEN matched_has_accepted THEN "*Accepted_name_author" |
953 | 991 |
ELSE "*Name_matched_author" |
954 | 992 |
END |
955 | 993 |
|
994 |
derived column |
|
995 |
|
|
956 | 996 |
to modify expr: |
957 | 997 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE |
958 | 998 |
WHEN matched_has_accepted THEN "*Accepted_name_author" |
... | ... | |
972 | 1012 |
-- |
973 | 1013 |
|
974 | 1014 |
COMMENT ON COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
975 |
derived column; = CASE
|
|
1015 |
= CASE |
|
976 | 1016 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
977 | 1017 |
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" |
978 | 1018 |
END |
979 | 1019 |
|
1020 |
derived column |
|
1021 |
|
|
980 | 1022 |
to modify expr: |
981 | 1023 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$CASE |
982 | 1024 |
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" |
trunk/schemas/util.sql | ||
---|---|---|
1990 | 1990 |
|
1991 | 1991 |
-- once CHECK constraint exists so that we can use its canon-ed formula |
1992 | 1992 |
SELECT util.set_comment(def.col, ' |
1993 |
derived column; = '||def.expr||'
|
|
1993 |
= '||def.expr/*on own line to support multiline exprs*/||'
|
|
1994 | 1994 |
|
1995 |
derived column |
|
1996 |
|
|
1995 | 1997 |
to modify expr: |
1996 | 1998 |
SELECT util.derived_col_update('||util.quote_typed(def)||'); |
1997 | 1999 |
SELECT util.derived_cols_populate('||util.quote_typed((def).col.table_)||'); |
trunk/schemas/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