PostgreSQL自定义类型与数组组合查询报错求助

首先，咱们先理清这个错误的根源：这个报错通常出现在你处理自定义数组/集合类型时，没有用PostgreSQL要求的语法来展开或查询它们，而RAISE NOTICE之所以能正常显示，是因为PL/pgSQL会自动帮你把复杂类型转成可读字符串，不需要手动处理。结合你给出的类型定义，我先补全推测的完整类型（毕竟你没写完T_COL_R），然后分场景给你正确的调用方式：

先补全常见的类型定义（结合你的代码） #

假设你的T_COL_R是t_col_foo的数组类型，完整定义应该是这样的：

CREATE OR REPLACE TYPE t_col_foo AS (
    ID NUMERIC,
    CLUSTERNAME VARCHAR(300),
    "1200AM" VARCHAR(10)
);
CREATE OR REPLACE TYPE T_COL_R AS t_col_foo[];

场景1：函数返回单个t_col_foo对象 #

如果你的函数是返回单个自定义对象（比如下面这个示例函数）：

CREATE OR REPLACE FUNCTION get_single_foo() RETURNS t_col_foo AS $$
BEGIN
    RETURN (1, 'cluster1', 'active')::t_col_foo;
END;
$$ LANGUAGE plpgsql;

正确的SELECT调用方式： #

1. 直接查询整个对象：

   SELECT get_single_foo();
   

   这会返回一行，格式是类似(1,cluster1,active)的对象字符串，不会报错。
2. 展开对象的单个字段（更实用）：
   如果你需要单独获取每个字段的值，可以用括号提取，或者用LATERAL更优雅地展开：

   -- 方式1：直接提取字段
   SELECT (get_single_foo()).id, (get_single_foo()).clustername, (get_single_foo())."1200AM";
   
   -- 方式2：用LATERAL简化写法
   SELECT foo.id, foo.clustername, foo."1200AM"
   FROM get_single_foo() AS foo;
   

场景2：函数返回T_COL_R数组类型 #

如果你的函数返回的是自定义对象的数组（比如下面这个示例）：

CREATE OR REPLACE FUNCTION get_foo_array() RETURNS T_COL_R AS $$
BEGIN
    RETURN ARRAY[
        (1, 'cluster1', 'active')::t_col_foo,
        (2, 'cluster2', 'inactive')::t_col_foo
    ]::T_COL_R;
END;
$$ LANGUAGE plpgsql;

这时候直接用SELECT get_foo_array();虽然能返回整个数组，但如果你想把数组里的每个对象拆成行，或者错误地用了不匹配的语法，就会触发你遇到的报错。正确的调用方式是用unnest()函数展开数组：

1. 把数组拆成多行对象：

   SELECT unnest(get_foo_array());
   

2. 同时展开数组并提取每个对象的字段：

   SELECT foo.id, foo.clustername, foo."1200AM"
   FROM unnest(get_foo_array()) AS foo;
   

额外排查点：类型匹配问题 #

还有一种常见情况：你的函数返回类型定义和实际返回值类型不匹配。比如函数定义返回t_col_foo，但你实际返回了一个数组，这时候RAISE NOTICE可能还能凑活显示，但SELECT时就会报类型错误。这种情况下，你需要：

• 检查函数的RETURNS子句，确保和实际返回值的类型完全一致；
• 如果返回的是数组，把返回类型改成T_COL_R（或者t_col_foo[]）；如果是单个对象，确保返回的不是数组。

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