Returning dynamic query results from PL/PGSQL function, multiple rows

Question

I work on the following test table:

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

I want to create a function which will accept group by column name and where predicate. Here is a working example of the function:

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';

and it's usage:

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

I'm trying to get not only a first row of the query but all the rows (obviously).

I tried the following:

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';

In both cases, with FOR or RETURN QUERY, the error occurs:

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

From the [docs][1]:

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

So, how is it possible to get the entire results?

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

Answer 1

I would return setof json instead of record because the number of result rows varies and json is more usable than record. So here is the function:

create function report2(arg_group text default '', arg_where text default '')
returns setof json language plpgsql as
$$
begin
  return query execute format(
    'select to_json(t) from (select %s sum(leads) from test %s %s) t', 
    case when arg_group = '' then '' else arg_group || ',' end,
    case when arg_where = '' then '' else 'where ' || arg_where end,
    case when arg_group = '' then '' else 'group by ' || arg_group end
   ); 
end; 
$$

select report2('broker');

report2
{"broker":"broker1","sum":3}
{"broker":"broker2","sum":4}

Answer 2

You're using a set-returning function in the select list. It's possible for SRF with return types that are either clearly specified or predictable, but your set-of-record-returning monstrosity doesn't qualify.

Technically, the function you presented is perfectly correct, fine and usable - you can call it, just not the way you tried. Look: (demo at db<>fiddle)

select * from report2('broker', '')as(col1 text,col2 bigint);

col1	col2
broker1	3
broker2	4

You can even get it to pack those back up into records:

select row(col1,col2) from report2('broker', '')as(col1 text,col2 bigint);

row
(broker1,3)
(broker2,4)

You can even build that into the function if you use the row() constructor to prevent unpacking:

     FOR result_row IN EXECUTE query
     LOOP
         RETURN NEXT row(result_row);
     END LOOP;

And then you can openly say it's returning just records (the non-of-your-business type):

select * from report2('broker', '')as(r record);

r
(broker1,3)
(broker2,4)

---

I have very little idea what it is you're trying to do, why and why this way, but polymorphic types would probably help, packing things into a jsonb suggested by @Stefanov.sm might be easier if you don't mind wrapping and unwrapping, casting and re-casting things back and forth. PL/pgSQL manual will definitely help.

---

If you want to json-wrap results, jsonb offers more performance through better indexing, more flexibility thanks to additional functions and operators than regular json, plus jsonb automatically cleans up insignificant whitespace, orders and deduplicates your keys, and is more compact to store&transfer.:

create function report2(group_by_column_name text default null, 
                        sql_where            text default 'true')
returns setof jsonb language plpgsql as $$ begin 
return query execute format(' 
  select to_jsonb(a) from (select %1$s sum(leads) from test where %2$s %3$s)a '
 , group_by_column_name||',' 
 , sql_where 
 , 'group by '||group_by_column_name
); end $$;

Default where true condition gets optimized away while saving argument checks. You don't have to check if the group by column is null/empty - if it is, || will nullify both the comma and group by literals. null||'something' yields null, and giving that to format() placeholder %s just doesn't put anything there.

The function runs the same, just in less text.