SELECT util.search_path_append('util');

SELECT util.rename_cols(:table_str, $$

cultivated => *cultivated,

flower     => *flower,

fruit      => *fruit,

$$::hstore);

UPDATE :table SET infrarank = NULL WHERE infrarank = 'L.'; -- runtime: ~0 ("Time: 2.370 ms") @starscream

-- UNCCD likely = UNCC database

$$_map('UNCCD=>UNCC, *=>*', "herbarium")$$)

; -- runtime: 1 s ("Time: 1057.222 ms") @starscream

SELECT mk_derived_col((:table_str, 'cultivated__campus'),

/* "this refers to occurrence on the UNCC campus, but I do not know the meaning

of the codes. Presumably C = cultivated"

(https://projects.nceas.ucsb.edu/nceas/attachments/download/723/UNCC-validate_rkp_bb_rkp.xlsx#Campus)

*/

$$_map('*=>true', "campus")$$)

; -- runtime: 0.5 s ("Time: 353.373 ms") @starscream

SELECT mk_derived_col((:table_str, 'cultivated__main'),

-- for our purposes, anything non-NULL should be assumed to be cultivated

$$_map('*=>true', "*cultivated")$$)

; -- runtime: 0.5 s ("Time: 403.916 ms") @starscream

CREATE OR REPLACE FUNCTION map_reproductive_condition(value text)

  RETURNS text AS

$BODY$

/*

"

Most are typos

A = absent

I = immature

M = mature

" (mailto:jim@habitatassessment.com?Habitat_Assessment.Jim_Matthews.to(uniola@email.unc.edu?Bob_Peet).2010-12-3:17:08.Re:_herbarium_phenology_codes_(fwd))

*/

SELECT util._map('A=>absent, I=>immature, M=>mature, *=>NULL', $1)

$BODY$

  LANGUAGE sql IMMUTABLE

  COST 100;

SELECT mk_derived_col((:table_str, 'flower'), $$map_reproductive_condition("*flower")$$); -- runtime: 0.5 s ("Time: 635.054 ms") @starscream

SELECT mk_derived_col((:table_str, 'fruit' ), $$map_reproductive_condition("*fruit" )$$); -- runtime: 0.5 s ("Time: 591.854 ms") @starscream