Project

General

Profile

« Previous | Next » 

Revision 14307

Added by Aaron Marcuse-Kubitza over 10 years ago

inputs/.TNRS/schema.sql: derived_cols_trigger_update(): removed lines between statements, which add too much space

View differences:

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

Also available in: Unified diff