Revision 14305
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 315 | 315 |
|
| 316 | 316 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT |
| 317 | 317 |
CASE |
| 318 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 319 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 320 |
ELSE "*Accepted_name_species"
|
|
| 321 |
END
|
|
| 318 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 319 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 320 |
ELSE "*Accepted_name_species" |
|
| 321 |
END |
|
| 322 | 322 |
FROM (SELECT new.*) new); |
| 323 | 323 |
|
| 324 | 324 |
RETURN new; |
| ... | ... | |
| 1079 | 1079 |
-- |
| 1080 | 1080 |
|
| 1081 | 1081 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
| 1082 |
= |
|
| 1083 |
CASE |
|
| 1084 |
WHEN "*Accepted_name_rank" = ''family''::text THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1085 |
WHEN "*Accepted_name_rank" = ''genus''::text THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1086 |
ELSE "*Accepted_name_species" |
|
| 1087 |
END |
|
| 1082 |
= CASE |
|
| 1083 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1084 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1085 |
ELSE "*Accepted_name_species" |
|
| 1086 |
END |
|
| 1088 | 1087 |
|
| 1089 | 1088 |
derived column |
| 1090 | 1089 |
|
| 1091 | 1090 |
to modify expr: |
| 1092 | 1091 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 1093 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1094 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1095 |
ELSE "*Accepted_name_species"
|
|
| 1096 |
END$$)::util.derived_col_def);
|
|
| 1092 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1093 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 1094 |
ELSE "*Accepted_name_species" |
|
| 1095 |
END$$)::util.derived_col_def); |
|
| 1097 | 1096 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 1098 | 1097 |
|
| 1099 | 1098 |
to rename: |
| trunk/schemas/util.sql | ||
|---|---|---|
| 1989 | 1989 |
enabled := util.derived_col_constraint_initially_enabled(def)); |
| 1990 | 1990 |
|
| 1991 | 1991 |
-- once CHECK constraint exists so that we can use its canon-ed formula |
| 1992 |
SELECT util.set_comment(def.col, '
|
|
| 1993 |
= '||def.expr/*on own line to support multiline exprs*/||'
|
|
| 1992 |
SELECT util.set_comment((def_).col, '
|
|
| 1993 |
= '||(def_).expr/*on own line to support multiline exprs*/||'
|
|
| 1994 | 1994 |
|
| 1995 | 1995 |
derived column |
| 1996 | 1996 |
|
| 1997 | 1997 |
to modify expr: |
| 1998 |
SELECT util.derived_col_update('||util.quote_typed(def)||');
|
|
| 1999 |
SELECT util.derived_cols_populate('||util.quote_typed((def).col.table_)||');
|
|
| 1998 |
SELECT util.derived_col_update('||util.quote_typed(def_)||');
|
|
| 1999 |
SELECT util.derived_cols_populate('||util.quote_typed((def_).col.table_)||');
|
|
| 2000 | 2000 |
|
| 2001 | 2001 |
to rename: |
| 2002 | 2002 |
# rename column |
| 2003 | 2003 |
# rename CHECK constraint |
| 2004 |
SELECT util.derived_cols_update('||util.quote_typed((def).col.table_)||');
|
|
| 2004 |
SELECT util.derived_cols_update('||util.quote_typed((def_).col.table_)||');
|
|
| 2005 | 2005 |
') |
| 2006 |
FROM (SELECT util.derived_col_def(def.col)/*expr from DB*/ AS def) s |
|
| 2006 |
-- **IMPORTANT**: vars can't have same name as params, which will be substituted |
|
| 2007 |
FROM (SELECT util.derived_col_def(def.col)/*expr from DB*/ AS def_) s |
|
| 2007 | 2008 |
; |
| 2008 | 2009 |
|
| 2009 | 2010 |
SELECT util.derived_cols_trigger_update((def).col.table_); |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19377 | 19377 |
|
| 19378 | 19378 |
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT |
| 19379 | 19379 |
CASE |
| 19380 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19381 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19382 |
ELSE "*Accepted_name_species"
|
|
| 19383 |
END
|
|
| 19380 |
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19381 |
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 19382 |
ELSE "*Accepted_name_species" |
|
| 19383 |
END |
|
| 19384 | 19384 |
FROM (SELECT new.*) new); |
| 19385 | 19385 |
|
| 19386 | 19386 |
RETURN new; |
| ... | ... | |
| 20183 | 20183 |
-- |
| 20184 | 20184 |
|
| 20185 | 20185 |
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS ' |
| 20186 |
= |
|
| 20187 |
CASE |
|
| 20188 |
WHEN "*Accepted_name_rank" = ''family''::text THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20189 |
WHEN "*Accepted_name_rank" = ''genus''::text THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20190 |
ELSE "*Accepted_name_species" |
|
| 20191 |
END |
|
| 20186 |
= CASE |
|
| 20187 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20188 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20189 |
ELSE "*Accepted_name_species" |
|
| 20190 |
END |
|
| 20192 | 20191 |
|
| 20193 | 20192 |
derived column |
| 20194 | 20193 |
|
| 20195 | 20194 |
to modify expr: |
| 20196 | 20195 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
| 20197 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20198 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20199 |
ELSE "*Accepted_name_species"
|
|
| 20200 |
END$$)::util.derived_col_def);
|
|
| 20196 |
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20197 |
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
|
| 20198 |
ELSE "*Accepted_name_species" |
|
| 20199 |
END$$)::util.derived_col_def); |
|
| 20201 | 20200 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 20202 | 20201 |
|
| 20203 | 20202 |
to rename: |
Also available in: Unified diff
bugfix: schemas/util.sql: derived_col_update(): set_comment(): vars can't have same name as params, which will be substituted