1 |
10245
|
aaronmk
|
SELECT util.search_path_append('util');
|
2 |
|
|
|
3 |
|
|
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY (specimen_id)$$);
|
4 |
10380
|
aaronmk
|
|
5 |
10382
|
aaronmk
|
|
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 |
10380
|
aaronmk
|
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 |
10381
|
aaronmk
|
|
27 |
10383
|
aaronmk
|
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
|