PostgreSQL用数组比对列值：同service_id下customer_id一致性校验的plpgsql实现问题

原代码核心问题 #

• 类型声明语法错误：PostgreSQL 数组类型的正确写法为基础类型[]，原代码中varchar array是无效语法；且通过row(service_id, customer_id)构造的是复合record类型，和声明的varchar字符串数组完全不兼容，根本无法正常取值。
• 逻辑偏离需求：校验目标是确认同一service_id下的customer_id是否一致，和该service_id关联的总记录数没有关系。原代码根据数组长度判断是否大于/小于2的逻辑完全跑题：哪怕一个service_id有10条关联记录，只要customer_id完全一致就是合格的；哪怕只有2条记录，只要customer_id不同就需要告警。
• 数组结构误解：array(select row(...) ...)生成的是一维record数组，每个数组元素是一整行记录，并非预期的多维结构，要拿到字段值必须通过record.字段名的方式拆解。
• 实现效率极低：手动管理游标、逐service_id查询明细的行级循环逻辑，没有利用SQL的集合运算优势，数据量稍大时性能远低于分组聚合方案。

最优实现方案 #

完全不需要逐行循环、不需要依赖数组比对，直接通过分组聚合一次性完成全量校验，性能最高：

-- 直接查询所有存在customer_id不一致问题的service_id
SELECT
  service_id,
  COUNT(DISTINCT customer_id) AS 不同customer_id数量
FROM services
GROUP BY service_id
-- 同一service下的去重customer_id数大于1，即为异常
HAVING COUNT(DISTINCT customer_id) > 1;

如果需要以存储过程形式输出告警日志，基于上述聚合逻辑改写即可，无需遍历每个service_id：

CREATE OR REPLACE PROCEDURE process_services()
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
BEGIN
  -- 仅遍历异常结果，全量校验一次完成
  FOR r IN
    SELECT
      service_id,
      COUNT(DISTINCT customer_id) AS distinct_customer_cnt
    FROM services
    GROUP BY service_id
    HAVING COUNT(DISTINCT customer_id) > 1
  LOOP
    RAISE NOTICE '警告：service_id = % 下存在%个不同的customer_id，数据校验不通过',
      r.service_id, r.distinct_customer_cnt;
  END LOOP;
END;
$$;

原代码修正版（仅参考，不推荐生产使用） #

如果必须沿用原有的逐service循环、数组比对的实现思路，修正后的可运行代码如下：

CREATE OR REPLACE PROCEDURE process_services()
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
  -- 修正数组类型：匹配行记录的存储类型
  services record[];
  customer_id_arr text[];
  distinct_customer_cnt int;
BEGIN
  -- 用plpgsql内置FOR循环替代手动管理游标，自动完成游标打开、遍历、关闭
  FOR r IN SELECT DISTINCT service_id FROM services LOOP
    -- 查询当前service下的所有记录，存入record数组
    SELECT ARRAY(
      SELECT row(service_id, customer_id)
      FROM services
      WHERE service_id = r.service_id
    ) INTO services;

    -- 从record数组中拆解出所有customer_id，生成纯customer_id数组
    SELECT ARRAY(
      SELECT (row_elem).customer_id
      FROM unnest(services) AS row_elem
    ) INTO customer_id_arr;

    -- 计算customer_id的去重数量，判断是否存在不一致
    SELECT COUNT(DISTINCT elem)
    INTO distinct_customer_cnt
    FROM unnest(customer_id_arr) AS elem;

    -- 按需求输出提示/告警
    IF distinct_customer_cnt > 1 THEN
      RAISE NOTICE '警告：service_id = % 下存在%个不同的customer_id',
        r.service_id, distinct_customer_cnt;
    ELSIF array_length(customer_id_arr, 1) = 1 THEN
      RAISE NOTICE '提示：service_id = % 仅关联1条customer_id记录', r.service_id;
    END IF;
  END LOOP;
END;
$$;

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