LANGUAGE plpgsql

BEGIN

    DROP TABLE IF EXISTS analytical_stem;

    CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0;

    GRANT SELECT ON TABLE analytical_stem TO bien_read;

    GRANT SELECT ON TABLE analytical_stem_view TO bien_read;

    ALTER TABLE analytical_stem ADD FOREIGN KEY (datasource) REFERENCES source (shortname) ON UPDATE CASCADE ON DELETE CASCADE;

    ALTER TABLE analytical_stem ALTER COLUMN "datasource" SET NOT NULL;

    ALTER TABLE analytical_stem ALTER COLUMN "locationID" SET NOT NULL;

    CREATE INDEX ON analytical_stem ("datasource", "occurrenceID");

    CREATE INDEX ON analytical_stem ("datasource", "institutionCode", "collectionCode", "catalogNumber");

    CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "dateCollected");

END;