Project

General

Profile

« Previous | Next » 

Revision 7173

Added by Aaron Marcuse-Kubitza over 11 years ago

schemas/vegbien.sql: sync_analytical_stem_to_view(): Drop and re-create dependent objects to avoid errors that analytical_stem can't be dropped because of dependents

View differences:

schemas/vegbien.sql
945 945 
CREATE FUNCTION sync_analytical_stem_to_view() RETURNS void
946 946 
    LANGUAGE plpgsql
947 947 
    AS $$
948 
DECLARE
949 
    -- Save dependent objects
950 
    analytical_aggregate_view_def text :=
951 
        'CREATE OR REPLACE VIEW analytical_aggregate_view AS '
952 
        ||pg_get_viewdef('analytical_aggregate_view');
953 
    provider_count_view_def text :=
954 
        'CREATE OR REPLACE VIEW provider_count_view AS '
955 
        ||pg_get_viewdef('provider_count_view');
948 956 
BEGIN
957 
    -- Drop dependent objects
958 
    DROP VIEW provider_count_view;
959 
    DROP VIEW analytical_aggregate_view;
960
949 961 
    DROP TABLE IF EXISTS analytical_stem;
950 962 
    CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view LIMIT 0;
951 963
......
960 972 
    CREATE INDEX ON analytical_stem ("datasource", "occurrenceID");
961 973 
    CREATE INDEX ON analytical_stem ("datasource", "institutionCode", "collectionCode", "catalogNumber");
962 974 
    CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "dateCollected");
975
976 
    -- Re-create dependent objects
977 
    EXECUTE analytical_aggregate_view_def;
978 
    GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;
979 
    EXECUTE provider_count_view_def;
980 
    GRANT SELECT ON TABLE provider_count_view TO bien_read;
981 
    GRANT SELECT ON TABLE provider_count_view TO public_;
963 982 
END;
964 983 
$$;
965 984 

  		












Also available in:  Unified diff