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

本是古典何须时尚 2023-10-12 12:29 21阅读 0赞

#### 文章目录 ####

*  代码
 *   *   *  创建物化视图表
         *  指定索引
         *  更新数据
         *  建立索引 & 建立触发器

## 代码 ##

#### 创建物化视图表 ####

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;