Project

General

Profile

« Previous | Next » 

Revision 14309

Added by Aaron Marcuse-Kubitza over 10 years ago

bugfix: schemas/util.sql: derived_cols_trigger_update(): need to clear derived cols first so old values won't be used in calculations

View differences:

trunk/inputs/.TNRS/schema.sql
195 195 
    LANGUAGE plpgsql
196 196 
    AS $$
197 197 
BEGIN
198 
	-- clear derived cols so old values won't be used in calculations
199 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
200 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
201 
	new.matched_has_accepted = NULL;
202 
	new."__accepted_{genus,specific_epithet}" = NULL;
203 
	new."[accepted_]genus__@DwC__@vegpath.org" = NULL;
204 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;
205 
	new.__accepted_infraspecific_label = NULL;
206 
	new."__accepted_infraspecific_{rank,epithet}" = NULL;
207 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
208 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
209 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
210 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL;
211 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
212 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL;
213 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL;
214 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
215 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
216 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
217 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
218 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
219 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
220 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;
221 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
222
223 
	-- populate derived cols
198 224 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
199 225 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
200 226 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
trunk/schemas/util.sql
2111 2111 
  RETURNS trigger AS
2112 2112 
$BODY1$
2113 2113 
BEGIN
2114 
	-- clear derived cols so old values won't be used in calculations
2114 2115 
$$||(
2115 2116 
	SELECT string_agg(
2117 
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = NULL;
2118 
$$, '')
2119 
	FROM util.derived_col_defs(table_) derived_col_def
2120 
)||
2121 
$$
2122 
	-- populate derived cols
2123 
$$||(
2124 
	SELECT string_agg(
2116 2125 
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = (SELECT $$||(derived_col_def).expr||$$ FROM (SELECT new.*) new);
2117 2126 
$$, '')
2118 2127 
	FROM util.derived_col_defs(table_) derived_col_def
trunk/schemas/vegbien.sql
19257 19257 
    LANGUAGE plpgsql
19258 19258 
    AS $$
19259 19259 
BEGIN
19260 
	-- clear derived cols so old values won't be used in calculations
19261 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19262 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
19263 
	new.matched_has_accepted = NULL;
19264 
	new."__accepted_{genus,specific_epithet}" = NULL;
19265 
	new."[accepted_]genus__@DwC__@vegpath.org" = NULL;
19266 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;
19267 
	new.__accepted_infraspecific_label = NULL;
19268 
	new."__accepted_infraspecific_{rank,epithet}" = NULL;
19269 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
19270 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
19271 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
19272 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL;
19273 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19274 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL;
19275 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL;
19276 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19277 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
19278 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
19279 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19280 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
19281 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
19282 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;
19283 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
19284
19285 
	-- populate derived cols
19260 19286 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
19261 19287 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
19262 19288 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);

Also available in: Unified diff