SELECT util.search_path_append('util');

SELECT create_if_not_exists($$CREATE INDEX "Specimen.acronym"   ON $$||:table_str||$$ (acronym)$$);
SELECT create_if_not_exists($$CREATE INDEX "Specimen.coll_year" ON $$||:table_str||$$ (coll_year)$$);
SELECT create_if_not_exists($$CREATE INDEX "Specimen.country"   ON $$||:table_str||$$ (country)$$);
SELECT create_if_not_exists($$CREATE INDEX "Specimen.long_deg"  ON $$||:table_str||$$ (long_deg)$$);

-- remove frameshifted rows
-- rerun time: 6 s ("Time: 5537.211 ms") thanks to index
DELETE FROM :table
WHERE
    acronym IS NULL
OR  coll_year !~ E'^(?:1[7-9]|20)\\d{2}$'
OR  country ~ E'\\d'
OR  (long_deg ~ E'[[:alpha:]]' AND long_deg NOT IN ('RESTRINGIDO'))
;

-- Remove institutions that we have direct data for
DELETE FROM :table
WHERE acronym IN (
    -- Comments are from e-mail from Brad Boyle on 2013-1-16
    'MO' -- "all MO records in REMIB are also available from MO's own website"
    --, 'ARIZ' -- Some REMIB ARIZ specimens not yet in ARIZ itself
    --, 'NY' -- Some REMIB NY specimens not yet in NY itself
    , 'TEX'
)
/* list obtained using the following on r9459:
SELECT DISTINCT dataprovider
FROM sourcelist
JOIN provider_count ON provider_count.dataprovider = sourcelist.name
WHERE source_id = source_by_shortname('REMIB')
ORDER BY dataprovider
*/
;