Project

General

Profile

« Previous | Next » 

Revision 14283

fix: schemas/util.sql: derived_col_update(): column comment: formula: put on own line to support multiline exprs

View differences:

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