code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0028_market_data_add_funding_rate.sql (about)

     1  -- +goose Up
     2  
     3  alter table market_data add column if not exists product_data JSONB;
     4  alter table current_market_data add column if not exists product_data JSONB;
     5  
     6  -- +goose StatementBegin
     7  CREATE OR REPLACE FUNCTION update_current_market_data()
     8      RETURNS TRIGGER
     9      LANGUAGE PLPGSQL AS
    10  $$
    11  BEGIN
    12  INSERT INTO current_market_data(synthetic_time,tx_hash,vega_time,seq_num,market,mark_price,best_bid_price,best_bid_volume,
    13                                  best_offer_price,best_offer_volume,best_static_bid_price,best_static_bid_volume,
    14                                  best_static_offer_price,best_static_offer_volume,mid_price,static_mid_price,open_interest,
    15                                  auction_end,auction_start,indicative_price,indicative_volume,market_trading_mode,
    16                                  auction_trigger,extension_trigger,target_stake,supplied_stake,price_monitoring_bounds,
    17                                  market_value_proxy,liquidity_provider_fee_shares,market_state,next_mark_to_market, market_growth, last_traded_price, product_data)
    18  VALUES(NEW.synthetic_time, NEW.tx_hash, NEW.vega_time, NEW.seq_num, NEW.market,
    19         NEW.mark_price, NEW.best_bid_price, NEW.best_bid_volume, NEW.best_offer_price,
    20         NEW.best_offer_volume, NEW.best_static_bid_price, NEW.best_static_bid_volume,
    21         NEW.best_static_offer_price, NEW.best_static_offer_volume, NEW.mid_price,
    22         NEW.static_mid_price, NEW.open_interest, NEW.auction_end, NEW.auction_start,
    23         NEW.indicative_price, NEW.indicative_volume, NEW.market_trading_mode,
    24         NEW.auction_trigger, NEW.extension_trigger, NEW.target_stake, NEW.supplied_stake,
    25         NEW.price_monitoring_bounds, NEW.market_value_proxy,
    26         NEW.liquidity_provider_fee_shares, NEW.market_state, NEW.next_mark_to_market, NEW.market_growth, NEW.last_traded_price, NEW.product_data)
    27      ON CONFLICT(market) DO UPDATE SET
    28      synthetic_time=EXCLUDED.synthetic_time,tx_hash=EXCLUDED.tx_hash,vega_time=EXCLUDED.vega_time,seq_num=EXCLUDED.seq_num,market=EXCLUDED.market,mark_price=EXCLUDED.mark_price,
    29                                 best_bid_price=EXCLUDED.best_bid_price,best_bid_volume=EXCLUDED.best_bid_volume,best_offer_price=EXCLUDED.best_offer_price,best_offer_volume=EXCLUDED.best_offer_volume,
    30                                 best_static_bid_price=EXCLUDED.best_static_bid_price,best_static_bid_volume=EXCLUDED.best_static_bid_volume,best_static_offer_price=EXCLUDED.best_static_offer_price,
    31                                 best_static_offer_volume=EXCLUDED.best_static_offer_volume,mid_price=EXCLUDED.mid_price,static_mid_price=EXCLUDED.static_mid_price,open_interest=EXCLUDED.open_interest,
    32                                 auction_end=EXCLUDED.auction_end,auction_start=EXCLUDED.auction_start,indicative_price=EXCLUDED.indicative_price,indicative_volume=EXCLUDED.indicative_volume,
    33                                 market_trading_mode=EXCLUDED.market_trading_mode,auction_trigger=EXCLUDED.auction_trigger,extension_trigger=EXCLUDED.extension_trigger,target_stake=EXCLUDED.target_stake,
    34                                 supplied_stake=EXCLUDED.supplied_stake,price_monitoring_bounds=EXCLUDED.price_monitoring_bounds,
    35                                 market_value_proxy=EXCLUDED.market_value_proxy,liquidity_provider_fee_shares=EXCLUDED.liquidity_provider_fee_shares,market_state=EXCLUDED.market_state,
    36                                 next_mark_to_market=EXCLUDED.next_mark_to_market,market_growth=EXCLUDED.market_growth,last_traded_price=EXCLUDED.last_traded_price,product_data=EXCLUDED.product_data;
    37  
    38  RETURN NULL;
    39  END;
    40  $$;
    41  -- +goose StatementEnd
    42  
    43  -- +goose Down
    44  
    45  -- +goose StatementBegin
    46  CREATE OR REPLACE FUNCTION update_current_market_data()
    47      RETURNS TRIGGER
    48      LANGUAGE PLPGSQL AS
    49  $$
    50  BEGIN
    51  INSERT INTO current_market_data(synthetic_time,tx_hash,vega_time,seq_num,market,mark_price,best_bid_price,best_bid_volume,
    52                                  best_offer_price,best_offer_volume,best_static_bid_price,best_static_bid_volume,
    53                                  best_static_offer_price,best_static_offer_volume,mid_price,static_mid_price,open_interest,
    54                                  auction_end,auction_start,indicative_price,indicative_volume,market_trading_mode,
    55                                  auction_trigger,extension_trigger,target_stake,supplied_stake,price_monitoring_bounds,
    56                                  market_value_proxy,liquidity_provider_fee_shares,market_state,next_mark_to_market, market_growth, last_traded_price)
    57  VALUES(NEW.synthetic_time, NEW.tx_hash, NEW.vega_time, NEW.seq_num, NEW.market,
    58         NEW.mark_price, NEW.best_bid_price, NEW.best_bid_volume, NEW.best_offer_price,
    59         NEW.best_offer_volume, NEW.best_static_bid_price, NEW.best_static_bid_volume,
    60         NEW.best_static_offer_price, NEW.best_static_offer_volume, NEW.mid_price,
    61         NEW.static_mid_price, NEW.open_interest, NEW.auction_end, NEW.auction_start,
    62         NEW.indicative_price, NEW.indicative_volume, NEW.market_trading_mode,
    63         NEW.auction_trigger, NEW.extension_trigger, NEW.target_stake, NEW.supplied_stake,
    64         NEW.price_monitoring_bounds, NEW.market_value_proxy,
    65         NEW.liquidity_provider_fee_shares, NEW.market_state, NEW.next_mark_to_market, NEW.market_growth, NEW.last_traded_price)
    66      ON CONFLICT(market) DO UPDATE SET
    67      synthetic_time=EXCLUDED.synthetic_time,tx_hash=EXCLUDED.tx_hash,vega_time=EXCLUDED.vega_time,seq_num=EXCLUDED.seq_num,market=EXCLUDED.market,mark_price=EXCLUDED.mark_price,
    68                                 best_bid_price=EXCLUDED.best_bid_price,best_bid_volume=EXCLUDED.best_bid_volume,best_offer_price=EXCLUDED.best_offer_price,best_offer_volume=EXCLUDED.best_offer_volume,
    69                                 best_static_bid_price=EXCLUDED.best_static_bid_price,best_static_bid_volume=EXCLUDED.best_static_bid_volume,best_static_offer_price=EXCLUDED.best_static_offer_price,
    70                                 best_static_offer_volume=EXCLUDED.best_static_offer_volume,mid_price=EXCLUDED.mid_price,static_mid_price=EXCLUDED.static_mid_price,open_interest=EXCLUDED.open_interest,
    71                                 auction_end=EXCLUDED.auction_end,auction_start=EXCLUDED.auction_start,indicative_price=EXCLUDED.indicative_price,indicative_volume=EXCLUDED.indicative_volume,
    72                                 market_trading_mode=EXCLUDED.market_trading_mode,auction_trigger=EXCLUDED.auction_trigger,extension_trigger=EXCLUDED.extension_trigger,target_stake=EXCLUDED.target_stake,
    73                                 supplied_stake=EXCLUDED.supplied_stake,price_monitoring_bounds=EXCLUDED.price_monitoring_bounds,
    74                                 market_value_proxy=EXCLUDED.market_value_proxy,liquidity_provider_fee_shares=EXCLUDED.liquidity_provider_fee_shares,market_state=EXCLUDED.market_state,
    75                                 next_mark_to_market=EXCLUDED.next_mark_to_market,market_growth=EXCLUDED.market_growth,last_traded_price=EXCLUDED.last_traded_price;
    76  
    77  RETURN NULL;
    78  END;
    79  $$;
    80  -- +goose StatementEnd
    81  
    82  alter table current_market_data drop column if exists product_data;
    83  alter table market_data drop column if exists product_data;