【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;