在postgresql中出现错误信息"记录类型未注册"，是什么问题？

Question

CREATE OR REPLACE FUNCTION get_biggest_order()
RETURNS TABLE(CustomerID int,Sum float) AS
$$
DECLARE
    rec RECORD;
mycursor CURSOR FOR WITH TOTAL(TotalPerCustomer) AS (SELECT SUM(TotalDue) FROM SalesOrderHeader GROUP BY SalesOrderHeader.CustomerID),
    MAX(Max) AS (SELECT MAX(TotalPerCustomer) FROM TOTAL)
    SELECT SalesOrderHeader.CustomerID,SUM(TotalDue) AS S FROM SalesOrderHeader,MAX GROUP BY SalesOrderHeader.CustomerID,Max HAVING SUM(TotalDue)=Max;
    BEGIN
        CREATE TEMP TABLE Results2(CustomerID int,Sum float);
        -- Open the cursor
        OPEN mycursor;
        LOOP
            -- fetch row into the film
            FETCH mycursor INTO rec;
            -- exit when no more row to fetch
            EXIT WHEN NOT FOUND;
            -- build the output
            INSERT INTO Results2 SELECT (rec).*;
        END LOOP;
    -- Close the cursor
        CLOSE mycursor;
        RETURN QUERY EXECUTE 'SELECT * FROM Results2';
    END; $$
LANGUAGE plpgsql;

SELECT get_biggest_order();

在Postgres中出现以下错误：

ERROR:  record type has not been registered
CONTEXT:  SQL statement "INSERT INTO Results2 SELECT (rec).*"
PL/pgSQL function get_biggest_order() line 17 at SQL statement
********** Error **********

ERROR: record type has not been registered
SQL state: 42809
Context: SQL statement "INSERT INTO Results2 SELECT (rec).*"
PL/pgSQL function get_biggest_order() line 17 at SQL statement

我正在尝试使用游标在表格格式中返回游标中的查询结果。显然，记录类型变量存在问题并且无法将其插入到临时表中，我希望使用该函数返回该表格。有什么问题吗？

- Kores

---

为什么要创建一个函数和一个临时表来执行一个简单的选择语句呢？ - Jorge Campos

这是一个任务，我也有同样的想法，哈哈 :) - Kores

我很好奇现在的老师们在想什么... 哈哈 - Jorge Campos

Answer 1

rec 是一个未经类型定义的记录，因此将 SELECT (rec).* 替换为 VALUES (rec.CustomerID, rec.S)。

- Ezequiel Tolnay