我在下面的测试表上工作：

select * from test;
 id | broker  | affiliate | leads 
----+---------+-----------+-------
  1 | broker1 | aff1      |     1
  2 | broker1 | aff2      |     2
  3 | broker2 | aff2      |     4
(3 rows)

字符串
我想创建一个函数，它将接受group by列名和where predicate 。下面是该函数的工作示例：

DROP FUNCTION report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS RECORD
AS $$
DECLARE
    query TEXT;
    result_row RECORD;
BEGIN
    query := 'SELECT ';
    IF group_by_column_name <> '' THEN
        query := query || group_by_column_name || ', ';
    END IF;
    query := query || 'sum(leads) FROM test';
    IF sql_where <> '' THEN
        query := query || ' WHERE ' || sql_where;
    END IF;
    IF group_by_column_name <> '' THEN
        query := query || ' GROUP BY(' || group_by_column_name || ')';
    END IF;
    RAISE NOTICE 'query: %;', query;

    

    EXECUTE query INTO result_row;
    RETURN result_row;

END
$$ LANGUAGE 'plpgsql';

型
它的用法：

select report2('broker', '');
NOTICE:  query: SELECT broker, sum(leads) FROM test GROUP BY(broker);
   report2   
-------------
 (broker1,3)
(1 row)

型
我不仅要获取查询的第一行，而且要获取所有行（显然）。
我尝试了以下方法：

DROP FUNCTION report2(TEXT,TEXT);
CREATE OR REPLACE FUNCTION report2(
    group_by_column_name TEXT,
    sql_where TEXT
)
RETURNS SETOF RECORD
AS $$
DECLARE
    query TEXT;
    result_row RECORD;
BEGIN
    query := 'SELECT ';
    IF group_by_column_name <> '' THEN
        query := query || group_by_column_name || ', ';
    END IF;
    query := query || 'sum(leads) FROM test';
    IF sql_where <> '' THEN
        query := query || ' WHERE ' || sql_where;
    END IF;
    IF group_by_column_name <> '' THEN
        query := query || ' GROUP BY(' || group_by_column_name || ')';
    END IF;
    RAISE NOTICE 'query: %;', query;

--  Does not worK:
--     FOR result_row IN EXECUTE query
--     LOOP
--         RETURN NEXT result_row;
--     END LOOP;

-- Does not worK:
--     RETURN QUERY EXECUTE query;


END
$$ LANGUAGE 'plpgsql';

型
在这两种情况下，对于FOR或RETURN QUERY，都会发生错误：

NOTICE:  query: SELECT broker, sum(leads) FROM test GROUP BY(broker);
ERROR:  materialize mode required, but it is not allowed in this context
CONTEXT:  PL/pgSQL function report2(text,text) line 21 at RETURN NEXT

型
从[docs][1]：

If multiple rows are returned, only the first will be assigned to the INTO variable(s).

型
那么，如何才能获得全部结果呢？

[1]: https://(https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)

型