PostgreSQL返回SETOF RECORD的函数调用报'materialize mode required'错误求助

问题场景 #

我创建了返回SETOF RECORD类型的my_func()函数，分别使用RETURN NEXT和RETURN QUERY语句实现，代码如下：

基于RETURN NEXT的实现 #

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES (ROW('John','Smith')), (ROW('David','Miller')) LOOP
    RETURN NEXT row;
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

基于RETURN QUERY的实现 #

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY VALUES (ROW('John','Smith')), (ROW('David','Miller'));
END;    
$$ LANGUAGE plpgsql;

但调用my_func()时出现如下错误：

postgres=# SELECT my_func();
ERROR:  materialize mode required, but it is not allowed in this context

我尝试将enable_material参数设置为on和off，均无法解决该错误：

postgres=# SELECT current_setting('enable_material');
 current_setting
-----------------
 on
(1 row)

postgres=# SELECT current_setting('enable_material');
 current_setting
-----------------
 off
(1 row)

此外，我创建的返回SETOF INT类型的同类函数（使用RETURN NEXT或RETURN QUERY语句）可以正常调用，代码如下：

返回SETOF INT的RETURN NEXT实现 #

CREATE FUNCTION my_func() RETURNS SETOF INT AS $$
DECLARE 
  num INT;
BEGIN
  FOR num IN VALUES (1), (2), (3) LOOP
    RETURN NEXT num;
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

返回SETOF INT的RETURN QUERY实现 #

CREATE FUNCTION my_func() RETURNS SETOF INT AS $$
BEGIN
  RETURN QUERY VALUES (1), (2), (3);
END;    
$$ LANGUAGE plpgsql;

错误原因 #

PostgreSQL中，RECORD是无固定结构的匿名复合类型。当函数返回SETOF RECORD时，PostgreSQL无法自动推断返回结果的列名和数据类型，导致执行查询时出现上述错误。而SETOF INT是明确的单一基础数据类型，不存在结构推断问题，因此可以正常调用。

解决方法 #

方法一：调用时显式指定返回结构 #

在调用函数时，通过AS子句明确指定RECORD的列名和对应数据类型，示例：

SELECT * FROM my_func() AS func_result(first_name text, last_name text);

方法二：定义自定义复合类型（推荐） #

先创建一个具有明确结构的复合类型，修改函数返回该类型的集合，这样调用时无需额外指定结构：

1. 创建复合类型：

CREATE TYPE person AS (
    first_name text,
    last_name text
);

2. 修改函数返回类型为SETOF person：

基于RETURN NEXT的修改版

CREATE FUNCTION my_func() RETURNS SETOF person AS $$
DECLARE 
  row person;
BEGIN
  FOR row IN VALUES (ROW('John','Smith')), (ROW('David','Miller')) LOOP
    RETURN NEXT row;
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

基于RETURN QUERY的修改版

CREATE FUNCTION my_func() RETURNS SETOF person AS $$
BEGIN
  RETURN QUERY VALUES (ROW('John','Smith')), (ROW('David','Miller'));
END;    
$$ LANGUAGE plpgsql;

3. 直接调用函数即可：

SELECT * FROM my_func();

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito