code.vegaprotocol.io/vega@v0.79.0/blockexplorer/store/migrations/0002_txresult_index.sql (about)

     1  -- +goose Up
     2  ALTER TABLE tx_results ADD column submitter TEXT;
     3  
     4  CREATE INDEX tx_results_tx_hash_index ON tx_results(tx_hash);
     5  CREATE INDEX tx_results_submitter_block_id_index_idx ON tx_results(submitter, block_id, index);
     6  CREATE INDEX attributes_value_index ON attributes(value, composite_key);
     7  
     8  -- +goose StatementBegin
     9  CREATE OR REPLACE FUNCTION update_txresult_submitter()
    10      RETURNS TRIGGER
    11      LANGUAGE PLPGSQL AS
    12  $$
    13  BEGIN
    14      UPDATE tx_results SET submitter=NEW.value
    15      FROM events e
    16      WHERE e.rowid = NEW.event_ID
    17      AND tx_results.rowid = e.tx_id;
    18      RETURN NEW;
    19  END;
    20  $$;
    21  -- +goose StatementEnd
    22  
    23  CREATE TRIGGER update_txresult_submitter AFTER INSERT ON attributes
    24      FOR EACH ROW
    25      WHEN (NEW.composite_key='tx.submitter')
    26      EXECUTE function update_txresult_submitter();
    27  
    28  
    29  -- +goose Down
    30  DROP TRIGGER update_txresult_submitter;
    31  DROP FUNCTION IF EXISTS update_txresult_submitter;
    32  
    33  DROP INDEX IF EXISTS attributes_value_index;
    34  DROP INDEX IF EXISTS tx_results_submitter_block_id_index_idx;
    35  DROP INDEX IF EXISTS tx_results_tx_hash_index;
    36  
    37  ALTER TABLE tx_results DROP COLUMN submitter;