SELECT util.search_path_append('util');

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

-- 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("diameterBreastHeight_min_cm_verbatim")$$); -- runtime: 0.2 s ("Time: 172.094 ms") @starscream

SELECT mk_derived_col((:table_str, 'diameterBreastHeight_max_cm'), $$map_nulls__nonzero("diameterBreastHeight_max_cm_verbatim")$$); -- runtime: 0.2 s ("Time: 161.774 ms") @starscream

SELECT mk_derived_col((:table_str, 'height_min_m'               ), $$map_nulls__nonzero("height_min_m_verbatim"               )$$); -- runtime: 0.2 s ("Time: 153.984 ms") @starscream

SELECT mk_derived_col((:table_str, 'height_max_m'               ), $$map_nulls__nonzero("height_max_m_verbatim"               )$$); -- runtime: 0.2 s ("Time: 207.515 ms") @starscream

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

$$CASE WHEN "locality__area__main" IS NOT NULL THEN "occurrenceRemarks_verbatim" END$$)

; -- runtime: 0.3 s ("Time: 311.190 ms") @starscream

SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("diameterBreastHeight_min_cm", "diameterBreastHeight_max_cm")$$); -- runtime: 0.3 s ("Time: 326.615 ms") @starscream

SELECT mk_derived_col((:table_str, 'height_m'),                $$_avg("height_min_m"               , "height_max_m"               )$$); -- runtime: 0.3 s ("Time: 346.548 ms") @starscream

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

$$_join("recordedBy_primary", "recordedBy_additional")$$)

; -- runtime: 0.3 s ("Time: 281.817 ms") @starscream

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

$$_join("locality__area__prefix", "locality__area__main", "locality__spot")$$)

; -- runtime: 0.3 s ("Time: 254.771 ms") @starscream