PostgreSQL触发器函数如何自动识别并获取表的任意主键列值？

核心改造逻辑 #

彻底移除硬编码的OLD.id/NEW.id取值逻辑，通过触发器运行时的内置上下文变量联动系统表，动态获取当前触发表的真实主键配置，全程不需要提前知晓表的主键列名：

• 利用触发器内置的TG_RELID变量直接拿到当前触发表的对象ID，不需要硬编码表名
• 关联pg_constraint、pg_attribute系统表，查询该表的主键约束对应的实际列名，兼容自定义命名、复合主键场景
• 通过jsonb格式转换行数据，动态提取对应主键列的值，不需要拼接动态SQL

可直接复用的函数实现 #

CREATE OR REPLACE FUNCTION trigger_crud_event_listener()
RETURNS TRIGGER AS $$
DECLARE
  pk_columns TEXT[];
  event_row_id TEXT;
  row_json JSONB;
BEGIN
  -- 动态查询当前表的所有主键列
  SELECT array_agg(a.attname ORDER BY a.attnum) INTO pk_columns
  FROM pg_constraint c
  JOIN pg_attribute a 
    ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
  WHERE c.conrelid = TG_RELID
    AND c.contype = 'p'
    AND a.attisdropped = false;

  -- 无主键的表直接兜底跳过
  IF pk_columns IS NULL THEN
    RAISE NOTICE '表 % 未配置主键约束，跳过CRUD事件上报', TG_TABLE_NAME;
    RETURN NULL;
  END IF;

  -- 按触发类型选择数据来源
  CASE TG_OP
    WHEN 'INSERT', 'UPDATE' THEN row_json := to_jsonb(NEW);
    WHEN 'DELETE' THEN row_json := to_jsonb(OLD);
  END CASE;

  -- 动态拼接所有主键值为row_id，兼容单/复合主键
  SELECT string_agg(row_json ->> col, '|') INTO event_row_id
  FROM unnest(pk_columns) col;

  -- 替换原有事件发送逻辑，直接使用event_row_id变量即可
  PERFORM pg_notify(
    'crud_event',
    jsonb_build_object(
      'table', TG_TABLE_NAME,
      'op', TG_OP,
      'row_id', event_row_id
    )::text
  );

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

适配场景说明 #

• 不管主键列是id、user_id、order_sn这类自定义命名，函数都能自动识别取值，不需要单独适配
• 复合主键场景下，会自动把所有主键列的值按约束顺序拼接为分隔字符串作为row_id，不需要额外修改逻辑
• 高频写表场景下可以通过plpgsql会话缓存存储每个TG_RELID对应的主键列，减少系统表重复查询的开销
• 对列名带特殊字符的场景，jsonb的按键取值逻辑完全兼容，不会出现列名转义导致的执行错误

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