PL/pgSQL函数返回TABLE(record)报错原因及疑问

报错的核心原因 #

当你把函数返回类型定义为TABLE(auth RECORD)时，PL/pgSQL无法确定这个record的具体结构——它不知道这个record包含哪些字段、每个字段的数据类型是什么。PostgreSQL在执行函数前需要明确返回结果的列定义，以此分配内存和处理数据流转，而未指定具体结构的匿名record无法被初始化赋值，因此抛出“record "auth" is not assigned yet”的错误。

为什么不能用record作为返回表的字段？ #

PostgreSQL的TABLE返回类型要求每一列的类型必须是结构确定、类型明确的：

• record本质是一种无固定结构的占位符，只有在被赋予具体数据（比如从查询结果赋值、或用ROW()构造并指定字段类型）时才会获得实际结构。但在函数的返回定义阶段，你没有提供这个record的结构信息，数据库无法提前规划返回结果的格式。
• 当你改成TABLE(auth TEXT, code INTEGER)时，每一列的类型和数量都完全明确，PostgreSQL可以预先知晓返回结果的格式，从而正确完成赋值和返回逻辑。

实现动态结构返回的替代方案 #

如果确实需要返回动态结构的结果，有两种可行方案：

• 使用RETURNS SETOF record，但调用函数时必须在查询中明确指定返回结构，示例：

  SELECT * FROM f_obtem_record_auth(some_record) AS (auth TEXT, code INTEGER);
  

• 自定义一个具体类型，再用这个类型作为返回字段的类型，示例：

  -- 先创建自定义类型
  CREATE TYPE auth_result AS (auth TEXT, code INTEGER);
  -- 函数返回定义改为TABLE(auth auth_result)
  CREATE OR REPLACE FUNCTION f_obtem_record_auth(input_record RECORD)
  RETURNS TABLE(auth auth_result) AS $$
  BEGIN
    -- 函数逻辑，比如赋值
    auth := ROW('test_auth', 200)::auth_result;
    RETURN NEXT;
  END;
  $$ LANGUAGE plpgsql;
  

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