实时将多源表数据同步至单表的解决方案咨询

你的问题核心在于：源应用仍在直接向table_*_legacy表插入数据，但你之前配置的规则仅针对table_*视图生效，因此新数据无法自动同步到table_all_signals。正确的做法是给每个table_*_legacy表添加触发器，当有数据插入/更新/删除时自动同步到汇总表。

步骤1：创建通用同步触发函数 #

先创建几个触发函数，分别处理插入、更新、删除操作，函数会自动从表名中提取对应的source_id：

插入同步函数 #

CREATE OR REPLACE FUNCTION sync_legacy_insert_to_all()
RETURNS TRIGGER AS $$
BEGIN
    -- 从表名（如table_1_legacy）中提取source_id
    INSERT INTO table_all_signals (source_id, signal_id, signal_timestamp, value)
    VALUES (
        SUBSTRING(TG_TABLE_NAME FROM 'table_(\d+)_legacy')::INT,
        NEW.signal_id,
        NEW.signal_timestamp,
        NEW.value
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

更新同步函数 #

CREATE OR REPLACE FUNCTION sync_legacy_update_to_all()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE table_all_signals
    SET signal_id = NEW.signal_id,
        signal_timestamp = NEW.signal_timestamp,
        value = NEW.value
    WHERE source_id = SUBSTRING(TG_TABLE_NAME FROM 'table_(\d+)_legacy')::INT
      AND signal_id = OLD.signal_id
      AND signal_timestamp = OLD.signal_timestamp
      AND value = OLD.value;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

删除同步函数 #

CREATE OR REPLACE FUNCTION sync_legacy_delete_to_all()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM table_all_signals
    WHERE source_id = SUBSTRING(TG_TABLE_NAME FROM 'table_(\d+)_legacy')::INT
      AND signal_id = OLD.signal_id
      AND signal_timestamp = OLD.signal_timestamp
      AND value = OLD.value;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

步骤2：为每个legacy表绑定触发器 #

给每个table_*_legacy表添加对应触发器，以table_1_legacy为例，其他表只需替换数字部分：

-- 插入触发器
CREATE TRIGGER trigger_table_1_legacy_insert
AFTER INSERT ON table_1_legacy
FOR EACH ROW
EXECUTE FUNCTION sync_legacy_insert_to_all();

-- 更新触发器（如果源应用有更新操作）
CREATE TRIGGER trigger_table_1_legacy_update
AFTER UPDATE ON table_1_legacy
FOR EACH ROW
EXECUTE FUNCTION sync_legacy_update_to_all();

-- 删除触发器（如果源应用有删除操作）
CREATE TRIGGER trigger_table_1_legacy_delete
AFTER DELETE ON table_1_legacy
FOR EACH ROW
EXECUTE FUNCTION sync_legacy_delete_to_all();

重复上述代码，将table_1_legacy替换为table_2_legacy到table_10_legacy，完成所有表的触发器配置。

补充说明 #

• 触发器采用AFTER触发，确保源表的操作成功后再同步到汇总表，避免数据不一致。
• 如果value字段存在重复值，仅用signal_id+signal_timestamp+value作为匹配条件可能不够精准，建议考虑给原表添加主键（比如自增ID），或者调整匹配逻辑。
• 你之前创建的视图和规则仍可保留，若后续应用改为操作视图，规则会继续生效。

内容的提问来源于stack exchange，提问作者Balaji