pgAdmin4中带自增外键关联的跨表插入存储过程实现

核心实现逻辑 #

不需要照搬Oracle的集合+逐行循环方案，PostgreSQL原生支持基于集合的批量写入，性能远高于行级循环，pgAdmin4环境下执行无语法兼容问题。
field表使用GENERATED BY DEFAULT AS IDENTITY定义的自增主键不影响当前逻辑：本次操作仅查询已存在的field_id做外键关联，不需要向field表写入新数据，无需手动处理自增值。 #

可直接运行的存储过程代码 #

代码内置源表去重、field_id批量匹配、重复插入拦截逻辑，直接替换业务字段名即可使用：

CREATE OR REPLACE PROCEDURE project.sync_sticky_plate_counts()
LANGUAGE plpgsql
AS $$
BEGIN
    WITH distinct_source AS (
        -- 按业务维度对源表去重，替换为你实际需要的去重字段、查询字段
        SELECT DISTINCT
            plate_sn,
            field_identifier, -- 传给lookup_field_id的匹配参数
            sticky_count,
            record_time
        FROM project.make_sticky_plate_counts
        WHERE field_identifier IS NOT NULL -- 过滤无匹配依据的脏数据
    ),
    matched_fields AS (
        SELECT
            ds.plate_sn,
            -- 批量调用自定义函数匹配field_id，STABLE/IMMUTABLE级别的函数会被PG自动优化执行
            project.lookup_field_id(ds.field_identifier) AS field_id,
            ds.sticky_count,
            ds.record_time
        FROM distinct_source ds
        -- 过滤匹配不到field_id的数据，避免触发外键约束报错
        WHERE project.lookup_field_id(ds.field_identifier) IS NOT NULL
    )
    INSERT INTO project.sticky_plates_fields (plate_id, field_id, count_value, record_time)
    SELECT
        mf.plate_sn,
        mf.field_id,
        mf.sticky_count,
        mf.record_time
    FROM matched_fields mf
    -- 防重复插入：替换为你目标表实际的唯一约束判断条件
    WHERE NOT EXISTS (
        SELECT 1
        FROM project.sticky_plates_fields spf
        WHERE spf.plate_id = mf.plate_sn
          AND spf.field_id = mf.field_id
    );
END;
$$;

调用方式 #

在pgAdmin4的查询窗口直接执行调用命令即可：

CALL project.sync_sticky_plate_counts();

试写代码常见错误修正 #

• 临时表取值无需逐行SELECT INTO赋值：所有数据清洗、匹配逻辑都可以在CTE或临时表中批量完成，最后一次性写入目标表，不要写逐行循环逻辑拖慢性能
• 自定义变量不要和表字段同名：避免PL/pgSQL标识符优先级导致的字段取值错误
• 如果lookup_field_id为VOLATILE级别（内部包含写入/临时表操作），改用临时表预计算的方式匹配，避免函数重复执行，参考代码片段如下：

  -- 临时表适配方案片段
  CREATE TEMP TABLE IF NOT EXISTS tmp_source ON COMMIT DROP AS
  SELECT DISTINCT plate_sn, field_identifier, sticky_count, record_time
  FROM project.make_sticky_plate_counts
  WHERE field_identifier IS NOT NULL;
  
  ALTER TABLE tmp_source ADD COLUMN field_id bigint;
  -- 批量匹配field_id
  UPDATE tmp_source SET field_id = project.lookup_field_id(field_identifier);
  
  -- 后续从tmp_source取数插入即可，逻辑和上述CTE方案一致
  

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