Project

General

Profile

« Previous | Next » 

Revision 14308

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): put expr on same line as var, to save space

View differences:

trunk/inputs/.TNRS/schema.sql
195 195 
    LANGUAGE plpgsql
196 196 
    AS $$
197 197 
BEGIN
198 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
199 
("*Genus_matched" || ' '::text) || "*Specific_epithet_matched"
200 
FROM (SELECT new.*) new);
201 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
202 
"*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text)
203 
FROM (SELECT new.*) new);
204 
	new.matched_has_accepted = (SELECT
205 
"*Accepted_name" IS NOT NULL
206 
FROM (SELECT new.*) new);
207 
	new."__accepted_{genus,specific_epithet}" = (SELECT
208 
regexp_split_to_array("*Accepted_name_species", ' '::text)
209 
FROM (SELECT new.*) new);
210 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT
211 
"__accepted_{genus,specific_epithet}"[1]
212 
FROM (SELECT new.*) new);
213 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT
214 
"__accepted_{genus,specific_epithet}"[2]
215 
FROM (SELECT new.*) new);
216 
	new.__accepted_infraspecific_label = (SELECT
217 
ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)
218 
FROM (SELECT new.*) new);
219 
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT
220 
regexp_split_to_array(__accepted_infraspecific_label, ' '::text)
221 
FROM (SELECT new.*) new);
222 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT
223 
"__accepted_infraspecific_{rank,epithet}"[1]
224 
FROM (SELECT new.*) new);
225 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT
226 
"__accepted_infraspecific_{rank,epithet}"[2]
227 
FROM (SELECT new.*) new);
228 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
229 
"*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text)
230 
FROM (SELECT new.*) new);
231 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT
232 
CASE
198 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
199 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
200 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
201 
	new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
202 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[1] FROM (SELECT new.*) new);
203 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
204 
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
205 
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
206 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
207 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
208 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
209 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE
233 210 
    WHEN matched_has_accepted THEN "*Accepted_name_rank"
234 211 
    ELSE "*Name_matched_rank"
235 
END
236 
FROM (SELECT new.*) new);
237 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
238 
CASE
212 
END FROM (SELECT new.*) new);
213 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
239 214 
    WHEN matched_has_accepted THEN "*Accepted_name_family"
240 215 
    ELSE "*Name_matched_accepted_family"
241 
END
242 
FROM (SELECT new.*) new);
243 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT
244 
CASE
216 
END FROM (SELECT new.*) new);
217 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT CASE
245 218 
    WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
246 219 
    ELSE "*Genus_matched"
247 
END
248 
FROM (SELECT new.*) new);
249 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT
250 
CASE
220 
END FROM (SELECT new.*) new);
221 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT CASE
251 222 
    WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
252 223 
    ELSE "*Specific_epithet_matched"
253 
END
254 
FROM (SELECT new.*) new);
255 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
256 
CASE
224 
END FROM (SELECT new.*) new);
225 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
257 226 
    WHEN matched_has_accepted THEN "*Accepted_name_species"
258 227 
    ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
259 
END
260 
FROM (SELECT new.*) new);
261 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT
262 
CASE
228 
END FROM (SELECT new.*) new);
229 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT CASE
263 230 
    WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
264 231 
    ELSE "*Infraspecific_rank"
265 
END
266 
FROM (SELECT new.*) new);
267 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT
268 
CASE
232 
END FROM (SELECT new.*) new);
233 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT CASE
269 234 
    WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
270 235 
    ELSE "*Infraspecific_epithet_matched"
271 
END
272 
FROM (SELECT new.*) new);
273 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
274 
CASE
236 
END FROM (SELECT new.*) new);
237 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
275 238 
    WHEN matched_has_accepted THEN "*Accepted_name"
276 239 
    ELSE "*Name_matched"
277 
END
278 
FROM (SELECT new.*) new);
279 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
280 
CASE
240 
END FROM (SELECT new.*) new);
241 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
281 242 
    WHEN matched_has_accepted THEN "*Accepted_name_author"
282 243 
    ELSE "*Name_matched_author"
283 
END
284 
FROM (SELECT new.*) new);
285 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
286 
CASE
244 
END FROM (SELECT new.*) new);
245 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT CASE
287 246 
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
288 247 
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
289 
END
290 
FROM (SELECT new.*) new);
291 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT
292 
"*Unmatched_terms"
293 
FROM (SELECT new.*) new);
294 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT
295 
CASE
248 
END FROM (SELECT new.*) new);
249 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
250 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
296 251 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
297 252 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
298 253 
    ELSE "*Accepted_name_species"
299 
END
300 
FROM (SELECT new.*) new);
254 
END FROM (SELECT new.*) new);
301 255
302 256 
	RETURN new;
303 257 
END;
trunk/schemas/util.sql
2113 2113 
BEGIN
2114 2114 
$$||(
2115 2115 
	SELECT string_agg(
2116 
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = (SELECT
2117 
$$||(derived_col_def).expr||$$
2118 
FROM (SELECT new.*) new);
2116 
$$	new.$$||quote_ident((derived_col_def).col.name)||$$ = (SELECT $$||(derived_col_def).expr||$$ FROM (SELECT new.*) new);
2119 2117 
$$, '')
2120 2118 
	FROM util.derived_col_defs(table_) derived_col_def
2121 2119 
)||
trunk/schemas/vegbien.sql
19257 19257 
    LANGUAGE plpgsql
19258 19258 
    AS $$
19259 19259 
BEGIN
19260 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
19261 
("*Genus_matched" || ' '::text) || "*Specific_epithet_matched"
19262 
FROM (SELECT new.*) new);
19263 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
19264 
"*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text)
19265 
FROM (SELECT new.*) new);
19266 
	new.matched_has_accepted = (SELECT
19267 
"*Accepted_name" IS NOT NULL
19268 
FROM (SELECT new.*) new);
19269 
	new."__accepted_{genus,specific_epithet}" = (SELECT
19270 
regexp_split_to_array("*Accepted_name_species", ' '::text)
19271 
FROM (SELECT new.*) new);
19272 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT
19273 
"__accepted_{genus,specific_epithet}"[1]
19274 
FROM (SELECT new.*) new);
19275 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT
19276 
"__accepted_{genus,specific_epithet}"[2]
19277 
FROM (SELECT new.*) new);
19278 
	new.__accepted_infraspecific_label = (SELECT
19279 
ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text)
19280 
FROM (SELECT new.*) new);
19281 
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT
19282 
regexp_split_to_array(__accepted_infraspecific_label, ' '::text)
19283 
FROM (SELECT new.*) new);
19284 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT
19285 
"__accepted_infraspecific_{rank,epithet}"[1]
19286 
FROM (SELECT new.*) new);
19287 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT
19288 
"__accepted_infraspecific_{rank,epithet}"[2]
19289 
FROM (SELECT new.*) new);
19290 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
19291 
"*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text)
19292 
FROM (SELECT new.*) new);
19293 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT
19294 
CASE
19260 
	new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
19261 
	new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Name_matched" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
19262 
	new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
19263 
	new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
19264 
	new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[1] FROM (SELECT new.*) new);
19265 
	new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
19266 
	new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''::text), ' '::text) FROM (SELECT new.*) new);
19267 
	new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
19268 
	new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
19269 
	new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
19270 
	new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
19271 
	new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE
19295 19272 
    WHEN matched_has_accepted THEN "*Accepted_name_rank"
19296 19273 
    ELSE "*Name_matched_rank"
19297 
END
19298 
FROM (SELECT new.*) new);
19299 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
19300 
CASE
19274 
END FROM (SELECT new.*) new);
19275 
	new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
19301 19276 
    WHEN matched_has_accepted THEN "*Accepted_name_family"
19302 19277 
    ELSE "*Name_matched_accepted_family"
19303 
END
19304 
FROM (SELECT new.*) new);
19305 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT
19306 
CASE
19278 
END FROM (SELECT new.*) new);
19279 
	new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT CASE
19307 19280 
    WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
19308 19281 
    ELSE "*Genus_matched"
19309 
END
19310 
FROM (SELECT new.*) new);
19311 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT
19312 
CASE
19282 
END FROM (SELECT new.*) new);
19283 
	new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT CASE
19313 19284 
    WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
19314 19285 
    ELSE "*Specific_epithet_matched"
19315 
END
19316 
FROM (SELECT new.*) new);
19317 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
19318 
CASE
19286 
END FROM (SELECT new.*) new);
19287 
	new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
19319 19288 
    WHEN matched_has_accepted THEN "*Accepted_name_species"
19320 19289 
    ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
19321 
END
19322 
FROM (SELECT new.*) new);
19323 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT
19324 
CASE
19290 
END FROM (SELECT new.*) new);
19291 
	new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT CASE
19325 19292 
    WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
19326 19293 
    ELSE "*Infraspecific_rank"
19327 
END
19328 
FROM (SELECT new.*) new);
19329 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT
19330 
CASE
19294 
END FROM (SELECT new.*) new);
19295 
	new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT CASE
19331 19296 
    WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
19332 19297 
    ELSE "*Infraspecific_epithet_matched"
19333 
END
19334 
FROM (SELECT new.*) new);
19335 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
19336 
CASE
19298 
END FROM (SELECT new.*) new);
19299 
	new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
19337 19300 
    WHEN matched_has_accepted THEN "*Accepted_name"
19338 19301 
    ELSE "*Name_matched"
19339 
END
19340 
FROM (SELECT new.*) new);
19341 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT
19342 
CASE
19302 
END FROM (SELECT new.*) new);
19303 
	new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
19343 19304 
    WHEN matched_has_accepted THEN "*Accepted_name_author"
19344 19305 
    ELSE "*Name_matched_author"
19345 
END
19346 
FROM (SELECT new.*) new);
19347 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT
19348 
CASE
19306 
END FROM (SELECT new.*) new);
19307 
	new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT CASE
19349 19308 
    WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
19350 19309 
    ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
19351 
END
19352 
FROM (SELECT new.*) new);
19353 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT
19354 
"*Unmatched_terms"
19355 
FROM (SELECT new.*) new);
19356 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT
19357 
CASE
19310 
END FROM (SELECT new.*) new);
19311 
	new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
19312 
	new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
19358 19313 
    WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "*Accepted_name_family", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
19359 19314 
    WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "*Accepted_name", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
19360 19315 
    ELSE "*Accepted_name_species"
19361 
END
19362 
FROM (SELECT new.*) new);
19316 
END FROM (SELECT new.*) new);
19363 19317
19364 19318 
	RETURN new;
19365 19319 
END;

Also available in: Unified diff