Postgres 12 PLPGSQL构造my_table数组报subquery must return only one column如何解决

错误原因 #

ARRAY()构造器要求输入的子查询必须返回单列，你写的SELECT mt.*会将my_table表的所有字段展开为多个独立列返回，不符合ARRAY构造的参数要求，因此触发42601报错。

正确写法 #

你需要让子查询返回单列的my_table复合类型值，两种常用实现方式如下：

1. 保留ARRAY构造语法，直接引用表别名mt作为返回列，不要加.*展开字段：

deleting := ARRAY(SELECT mt FROM table1 t1
  INNER JOIN table2 t2 ON t2.id=t1.table1
  INNER JOIN my_table mt ON mt.id=t2.my_table
  WHERE t1.x=10);

2. 使用array_agg聚合函数实现，逻辑更直观：

SELECT array_agg(mt) INTO deleting 
FROM table1 t1
INNER JOIN table2 t2 ON t2.id=t1.table1
INNER JOIN my_table mt ON mt.id=t2.my_table
WHERE t1.x=10;

两种写法都可在PostgreSQL 12环境正常运行，最终得到的deleting数组完全符合my_table[]的类型要求。

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