Project

General

Profile

« Previous | Next » 

Revision 14306

Added by Aaron Marcuse-Kubitza over 10 years ago

fix: schemas/util.sql: derived_cols_trigger_update(): don't indent expr because may have multiple lines

View differences:

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

Also available in: Unified diff