LANGUAGE plpgsql

    AS $$

DECLARE

    analytical_plot_def text := NULL;

    analytical_specimen_def text := NULL;

    provider_count_view_def text := NULL;

    range_modeling_input_def text := NULL;

BEGIN

    -- Save and drop dependent objects

    BEGIN

        analytical_plot_def :=

            'CREATE OR REPLACE VIEW analytical_plot AS '

            ||pg_get_viewdef('analytical_plot');

        DROP VIEW analytical_plot;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

        analytical_specimen_def :=

            'CREATE OR REPLACE VIEW analytical_specimen AS '

            ||pg_get_viewdef('analytical_specimen');

        DROP VIEW analytical_specimen;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

        provider_count_view_def :=

            'CREATE OR REPLACE VIEW provider_count_view AS '

            ||pg_get_viewdef('provider_count_view');

        DROP VIEW provider_count_view;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    BEGIN

        range_modeling_input_def :=

            'CREATE OR REPLACE VIEW range_modeling_input AS '

            ||pg_get_viewdef('range_modeling_input');

        DROP VIEW range_modeling_input;

    EXCEPTION

        WHEN undefined_table THEN NULL;

    END;

    DROP TABLE IF EXISTS analytical_stem;

    CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0;

    ALTER TABLE analytical_stem ADD COLUMN taxon_occurrence__pkey serial PRIMARY KEY;

    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", "specimenHolderInstitutions", "collection", "accessionNumber");

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

    CREATE INDEX ON analytical_stem ("scrubbed_morphospecies_binomial");

    -- Re-create dependent objects

    IF analytical_plot_def IS NOT NULL THEN

        EXECUTE analytical_plot_def;

        GRANT SELECT ON TABLE analytical_plot TO bien_read;

    END IF;

    IF analytical_specimen_def IS NOT NULL THEN

        EXECUTE analytical_specimen_def;

        GRANT SELECT ON TABLE analytical_specimen TO bien_read;

    END IF;

    IF provider_count_view_def IS NOT NULL THEN

        EXECUTE provider_count_view_def;

        GRANT SELECT ON TABLE provider_count_view TO bien_read;

        GRANT SELECT ON TABLE provider_count_view TO public_;

    END IF;

    IF range_modeling_input_def IS NOT NULL THEN

        EXECUTE range_modeling_input_def;

        GRANT SELECT ON TABLE range_modeling_input TO bien_read;

    END IF;

END;

$$;