github.com/pf-qiu/concourse/v6@v6.7.3-0.20201207032516-1f455d73275f/atc/db/migration/migrations/1530824006_create_pipelines_trigger.up.sql (about)

     1  BEGIN;
     2    CREATE OR REPLACE FUNCTION on_pipeline_delete() RETURNS TRIGGER AS $$
     3    BEGIN
     4            EXECUTE format('DROP TABLE IF EXISTS pipeline_build_events_%s', OLD.id);
     5            RETURN NULL;
     6    END;
     7    $$ LANGUAGE plpgsql;
     8  
     9  
    10    CREATE OR REPLACE FUNCTION on_pipeline_insert() RETURNS TRIGGER AS $$
    11    BEGIN
    12            EXECUTE format('CREATE TABLE IF NOT EXISTS pipeline_build_events_%s () INHERITS (build_events)', NEW.id);
    13            EXECUTE format('CREATE INDEX IF NOT EXISTS pipeline_build_events_%s_build_id ON pipeline_build_events_%s (build_id)', NEW.id, NEW.id);
    14            EXECUTE format('CREATE UNIQUE INDEX IF NOT EXISTS pipeline_build_events_%s_build_id_event_id ON pipeline_build_events_%s (build_id, event_id)', NEW.id, NEW.id);
    15            RETURN NULL;
    16    END;
    17    $$ LANGUAGE plpgsql;
    18  
    19  
    20    DROP TRIGGER IF EXISTS pipeline_build_events_delete_trigger ON pipelines;
    21    CREATE TRIGGER pipeline_build_events_delete_trigger AFTER DELETE on pipelines FOR EACH ROW EXECUTE PROCEDURE on_pipeline_delete();
    22  
    23    DROP TRIGGER IF EXISTS pipeline_build_events_insert_trigger ON pipelines;
    24    CREATE TRIGGER pipeline_build_events_insert_trigger AFTER INSERT on pipelines FOR EACH ROW EXECUTE PROCEDURE on_pipeline_insert();
    25  COMMIT;