|
1
|
SELECT util.search_path_append('util');
|
|
2
|
|
|
3
|
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY (specimen_id)$$);
|
|
4
|
|
|
5
|
|
|
6
|
-- map_nulls() derived cols
|
|
7
|
-- runtime: 1 s @starscream
|
|
8
|
|
|
9
|
CREATE OR REPLACE FUNCTION map_nulls__nonzero(value anyelement)
|
|
10
|
RETURNS anyelement AS
|
|
11
|
$BODY$
|
|
12
|
SELECT NULLIF($1, 0)
|
|
13
|
$BODY$
|
|
14
|
LANGUAGE sql IMMUTABLE
|
|
15
|
COST 100;
|
|
16
|
|
|
17
|
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_min_cm'), $$map_nulls__nonzero("DBH_Min" )$$); -- runtime: 0.2 s ("Time: 172.094 ms") @starscream
|
|
18
|
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_max_cm'), $$map_nulls__nonzero("DBH_Max" )$$); -- runtime: 0.2 s ("Time: 161.774 ms") @starscream
|
|
19
|
SELECT mk_derived_col((:table_str, 'height_min_m' ), $$map_nulls__nonzero("Height_Min")$$); -- runtime: 0.2 s ("Time: 153.984 ms") @starscream
|
|
20
|
SELECT mk_derived_col((:table_str, 'height_max_m' ), $$map_nulls__nonzero("Height_Max")$$); -- runtime: 0.2 s ("Time: 207.515 ms") @starscream
|
|
21
|
|
|
22
|
|
|
23
|
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),
|
|
24
|
$$CASE WHEN "Locality_Description" IS NOT NULL THEN "Notes_Plant" END$$)
|
|
25
|
; -- runtime: 0.3 s ("Time: 311.190 ms") @starscream
|
|
26
|
|
|
27
|
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("DBH_Min" , "DBH_Max" )$$); -- runtime: 0.3 s ("Time: 326.615 ms") @starscream
|
|
28
|
SELECT mk_derived_col((:table_str, 'height_m'), $$_avg("Height_Min", "Height_Max")$$); -- runtime: 0.3 s ("Time: 346.548 ms") @starscream
|
