PostgreSQL子查询多列报错multiple columns in subquery修复方法

问题复现 #

你编写的函数代码如下：

CREATE OR REPLACE FUNCTION public.drivers_have_unsatisfied_documents()
 RETURNS driver_expiring_documents
AS $function$
DECLARE
    unsatified_documents driver_expiring_documents;
    expiring_doc driver_expiring_documents%rowtype;
    expiring_doc_driver_ids text[];
    expired_doc_driver_ids text[];
    expiring_date date;
    driver_id_arg text;
BEGIN
    unsatified_documents := array(
    select expiration_date, driver_id from all_requirements_driver_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    union
    select expiration_date, driver_id from all_requirements_vehicle_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    );
    -- Do some more stuff, code intentionally removed ---
    return unsatified_documents;
end;
$function$ stable language 'plpgsql';

运行时抛出错误：

multiple columns in subquery

错误根因 #

这个错误由两个写法问题共同导致：

• PostgreSQL的ARRAY()构造函数有强制要求：括号内的子查询只能返回单个列。你的子查询同时返回了expiration_date、driver_id两个独立列，不符合构造器的入参要求，直接触发当前报错。
• 变量类型声明错误：你要存储的是多条复合类型记录的集合，属于数组类型，但声明unsatified_documents时只写了复合类型名driver_expiring_documents，缺少数组标识[]，就算解决了多列问题，这里也会触发类型不匹配错误。另外注意函数返回值如果是要返回复合类型数组，也需要同步加上[]标识。

修复方案 #

• 给子查询的多列加上行构造器ROW()，把两列打包成单个driver_expiring_documents复合类型值，满足ARRAY()构造器单返回列的要求。
• 修正变量、函数返回值的类型声明，给复合类型加上数组标识[]。

修复后的完整代码如下：

CREATE OR REPLACE FUNCTION public.drivers_have_unsatisfied_documents()
 -- 如果函数要返回数组，这里必须加[]
 RETURNS driver_expiring_documents[]
AS $function$
DECLARE
    -- 变量声明加[]，标识为复合类型数组
    unsatified_documents driver_expiring_documents[];
    expiring_doc driver_expiring_documents%rowtype;
    expiring_doc_driver_ids text[];
    expired_doc_driver_ids text[];
    expiring_date date;
    driver_id_arg text;
BEGIN
    unsatified_documents := array(
    -- 用ROW()把两列打包为单个复合类型值
    select ROW(expiration_date, driver_id)::driver_expiring_documents from all_requirements_driver_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    union
    select ROW(expiration_date, driver_id)::driver_expiring_documents from all_requirements_vehicle_documents where expiration_date <= (now() + interval '1 month')::DATE and expiration_date > (now() - interval '7 day')::DATE
    );
    -- Do some more stuff, code intentionally removed ---
    return unsatified_documents;
end;
$function$ stable language 'plpgsql';

补充说明：如果driver_expiring_documents本身不是数组类型，而是你原本打算用SETOF返回结果集，那不需要用ARRAY()赋值，直接用RETURN QUERY + 原UNION查询即可，不需要声明数组变量。

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