我正在构建一个触发器来跟踪进入我的 postgresql/timescaleDB 表中的每个符号的最新时间戳。
表创建定义如下所示：

create table symbol_cache (
  symbol text not null primary key,
  timestamp timestamptz not null
)

create or replace function refresh_symbol_cache() returns trigger
    language plpgsql
as $$
BEGIN
    INSERT INTO symbol_cache (symbol, timestamp)
    VALUES (
               NEW.symbol,
               NEW.timestamp
           )
    ON CONFLICT (symbol)
        DO UPDATE SET
                      symbol = NEW.symbol,
                      timestamp = GREATEST(old.timestamp, new.timestamp);
    RETURN NEW;
END;
$$;


create trigger update_symbol_cache
    after insert or update
    on db_009a005a_df_downloaded_grand
    for each row
execute procedure refresh_symbol_cache();




复制