| 1 |
10245
|
aaronmk
|
SELECT util.search_path_append('util');
|
| 2 |
|
|
|
| 3 |
10386
|
aaronmk
|
SELECT create_if_not_exists($$ALTER TABLE $$||:table_str||$$ ADD PRIMARY KEY ("dataProviderRecordID")$$);
|
| 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 |
10386
|
aaronmk
|
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
|
| 18 |
|
|
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
|
| 19 |
|
|
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
|
| 20 |
|
|
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
|
| 21 |
10382
|
aaronmk
|
|
| 22 |
|
|
|
| 23 |
10380
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'occurrenceRemarks'),
|
| 24 |
10386
|
aaronmk
|
$$CASE WHEN "locality__area__main" IS NOT NULL THEN "occurrenceRemarks_verbatim" END$$)
|
| 25 |
10380
|
aaronmk
|
; -- runtime: 0.3 s ("Time: 311.190 ms") @starscream
|
| 26 |
10381
|
aaronmk
|
|
| 27 |
10387
|
aaronmk
|
SELECT mk_derived_col((:table_str, 'diameterBreastHeight_cm'), $$_avg("diameterBreastHeight_min_cm", "diameterBreastHeight_max_cm")$$); -- runtime: 0.3 s ("Time: 326.615 ms") @starscream
|
| 28 |
|
|
SELECT mk_derived_col((:table_str, 'height_m'), $$_avg("height_min_m" , "height_max_m" )$$); -- runtime: 0.3 s ("Time: 346.548 ms") @starscream
|
| 29 |
10385
|
aaronmk
|
|
| 30 |
|
|
SELECT mk_derived_col((:table_str, 'recordedBy'),
|
| 31 |
10386
|
aaronmk
|
$$_join("recordedBy_primary", "recordedBy_additional")$$)
|
| 32 |
10385
|
aaronmk
|
; -- runtime: 0.3 s ("Time: 281.817 ms") @starscream
|
| 33 |
|
|
SELECT mk_derived_col((:table_str, 'locality'),
|
| 34 |
10386
|
aaronmk
|
$$_join("locality__area__prefix", "locality__area__main", "locality__spot")$$)
|
| 35 |
10385
|
aaronmk
|
; -- runtime: 0.3 s ("Time: 254.771 ms") @starscream
|
