SELECT util.search_path_append('util');

SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY (specimen_id)$$);


-- map_nulls() derived cols
-- runtime: 1 s @starscream

CREATE OR REPLACE FUNCTION map_nulls__nonzero(value anyelement)
  RETURNS anyelement AS
$BODY$
SELECT NULLIF($1, 0)
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

SELECT mk_derived_col((:table_str, 'diameterBreastHeight_min_cm'), $$map_nulls__nonzero("DBH_Min"   )$$); -- runtime: 0.2 s ("Time: 172.094 ms") @starscream
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_max_cm'), $$map_nulls__nonzero("DBH_Max"   )$$); -- runtime: 0.2 s ("Time: 161.774 ms") @starscream
SELECT mk_derived_col((:table_str, 'height_min_m'               ), $$map_nulls__nonzero("Height_Min")$$); -- runtime: 0.2 s ("Time: 153.984 ms") @starscream
SELECT mk_derived_col((:table_str, 'height_max_m'               ), $$map_nulls__nonzero("Height_Max")$$); -- runtime: 0.2 s ("Time: 207.515 ms") @starscream


SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),
$$CASE WHEN "Locality_Description" IS NOT NULL THEN "Notes_Plant" END$$)
; -- runtime: 0.3 s ("Time: 311.190 ms") @starscream

SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("DBH_Min"   , "DBH_Max"   )$$); -- runtime: 0.3 s ("Time: 326.615 ms") @starscream
SELECT mk_derived_col((:table_str, 'height_m'),                $$_avg("Height_Min", "Height_Max")$$); -- runtime: 0.3 s ("Time: 346.548 ms") @starscream