【sql】PostgreSQL物化视图表使用案例

create materialized view sel_eth_txn_info_recent_30_day as
select
  *
from
  sel_eth_txn_info set2  
where
  date(to_timestamp(block_time / 1000)::text) >= CURRENT_DATE - interval '30 days'
with no data;

CREATE UNIQUE INDEX sel_eth_txn_info_recent_30_day_id ON sel_eth_txn_info_recent_30_day(id);

REFRESH MATERIALIZED VIEW CONCURRENTLY sel_eth_txn_info_recent_30_day; -- 边刷新、边查
REFRESH MATERIALIZED VIEW sel_eth_txn_info_recent_30_day;

---
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (block_time);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (contract_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (from_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (to_address);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (transaction_hash);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (transaction_type);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (value);
CREATE INDEX  ON public.sel_eth_txn_info_recent_30_day USING btree (block_number);
---
CREATE OR REPLACE FUNCTION update_sel_eth_txn_info_recent_30_day()
RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.block_time / 1000 >= CURRENT_DATE - INTERVAL '30 days') THEN
      INSERT INTO sel_eth_txn_info_recent_30_day VALUES (NEW.*);
    END IF;
    -- delete expired data
    DELETE FROM sel_eth_txn_info_recent_30_day WHERE block_time / 1000 < CURRENT_DATE - INTERVAL '30 days';
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (NEW.block_time / 1000 >= CURRENT_DATE - INTERVAL '30 days') THEN
      UPDATE sel_eth_txn_info_recent_30_day SET block_time = NEW.block_time, 
    block_number = NEW.block_number,
    transaction_hash = NEW.transaction_hash,
    contract_address = NEW.contract_address,
    from_address = NEW.from_address,
    value = NEW.value,
    transaction_type = NEW.transaction_type WHERE id = NEW.id;
    ELSE
      DELETE FROM sel_eth_txn_info_recent_30_day WHERE id = NEW.id;
    END IF;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM sel_eth_txn_info_recent_30_day WHERE id = OLD.id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
---
CREATE TRIGGER update_sel_eth_txn_info_recent_30_day
AFTER INSERT OR UPDATE OR DELETE ON sel_eth_txn_info
FOR EACH ROW EXECUTE PROCEDURE update_sel_eth_txn_info_recent_30_day();
---
REFRESH MATERIALIZED VIEW sel_eth_txn_info_recent_30_day;