如何将查询结果加载至PostgreSQL复合类型数组 免循环完成存储

核心实现逻辑 #

整个实现不需要任何逐行循环语句，所有处理都是集合级操作。利用PostgreSQL原生的array_agg聚合能力，在单条集合查询中完成「查询结果列→自定义复合类型→数组聚合」的全流程处理，全程由数据库执行引擎完成运算，无PL/pgSQL层面的逐行上下文切换，性能和直接执行原始查询几乎没有差异。

可直接落地的代码 #

DO $$
DECLARE
  -- 按需求声明的type1数组变量
  arr_type1 type1[];
BEGIN
  -- 单条查询完成全量加载，无逐行循环逻辑
  SELECT 
    COALESCE(
      -- 逐行构造匹配type1结构的复合值后，聚合为数组
      array_agg(ROW(st_id, st_name, recorded_dt)::type1),
      -- 空结果集兜底，避免返回NULL值
      ARRAY[]::type1[]
    )
  INTO arr_type1
  FROM st_samples;

  -- 可选：结果校验代码，可根据需要保留
  RAISE NOTICE '共加载%条数据到arr_type1数组', COALESCE(array_length(arr_type1, 1), 0);
END $$;

关键注意事项 #

• 复合类型匹配规则：ROW()构造里的列顺序、数据类型必须和预先定义的type1(id bigint, name varchar(4000), created_on date)严格一一对应：
  • 第一个位置放st_id（bigint类型，对应type1的id字段）
  • 第二个位置放st_name（varchar类型，对应type1的name字段）
  • 第三个位置放recorded_dt（date类型，对应type1的created_on字段）
    只要顺序和类型匹配，就可以直接通过::type1完成类型转换，不需要额外的逐行映射逻辑。
• 类型兼容规则：只要源查询列的类型和type1对应字段的类型满足PostgreSQL的隐式转换规则，就可以直接映射；如果存在类型不兼容的场景（比如源列是文本格式的数字），需要在ROW构造时显式转换为对应字段类型，示例：ROW(st_id::bigint, st_name::varchar(4000), recorded_dt::date)::type1。
• 空结果兼容：如果查询无返回数据，array_agg默认会返回NULL，通过COALESCE兜底返回空的type1数组，可避免后续访问数组时因NULL值触发运行时报错。
• 性能说明：这种实现方式是在SQL执行层完成的聚合，和直接运行原始查询的性能差异极小，远高于逐行循环拼接数组的方案。
• 简化写法：如果查询列的顺序、类型和type1完全匹配，也可以省略显式ROW构造，直接将子查询的行值转换为type1后聚合，示例代码：

  SELECT COALESCE(array_agg(ts.*::type1), ARRAY[]::type1[])
  INTO arr_type1
  FROM (SELECT st_id, st_name, recorded_dt FROM st_samples) ts;
  

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